UPDATE and DELETE across two tables

The form that runs everywhere

The WHERE clause of an UPDATE is an ordinary WHERE clause, so a subquery can pick the rows. No engine needs anything special for this.

-- Works on every engine in this list, unchanged.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded' AND total > 2000
);

When the new value itself comes from the other table, a correlated subquery in SET does that too:

-- The same shape when the new value comes from the other table.
UPDATE products
SET stock_quantity = stock_quantity + (
  SELECT COALESCE(SUM(change_qty), 0)
  FROM inventory_logs il
  WHERE il.product_id = products.id
    AND il.reason = 'return'
)
WHERE id IN (SELECT product_id FROM inventory_logs WHERE reason = 'return');

Note the repeated condition. The subquery in SET decides the value, and the one in WHERE decides which rows are touched at all — without the second, every product would be updated and the ones with no matching log would be set using COALESCE(..., 0), adding nothing but still writing the row.

This form is portable and it is readable. It is not always the fastest: an engine may run the correlated subquery once per row, which is what the plan will tell you. The join syntaxes below exist because on large updates that difference is worth the loss of portability.

MySQL and MariaDB

-- The join goes between UPDATE and SET.
UPDATE customers c
  JOIN orders o ON o.customer_id = c.id
SET c.loyalty_points = c.loyalty_points + 10
WHERE o.status = 'refunded';

-- DELETE names which table's rows to remove, before FROM.
DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

The join sits between UPDATE and SET, which is unlike every other engine here. DELETE needs the target named before FROM — DELETE c FROM customers c JOIN ... — because with two tables joined the statement would otherwise be ambiguous about whose rows to remove. Leaving that alias out is a syntax error, which is the good case; naming the wrong one is not.

PostgreSQL

-- The second table goes in FROM, and the join condition in WHERE.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded';

-- DELETE uses USING rather than FROM for the extra table.
DELETE FROM wishlists
USING products p
WHERE p.id = wishlists.product_id
  AND p.stock_quantity = 0;

The extra table goes in FROM and the join condition moves into WHERE. Two details are easy to get wrong: the target table is not repeated in FROM (doing so joins it to itself), and DELETE uses USING where UPDATE uses FROM.

SQLite adopted UPDATE … FROM with PostgreSQL's semantics in 3.33 (2020), which is why the third example on this page runs here:

-- SQLite 3.33 (2020) and later, with PostgreSQL's spelling.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded'
  AND o.total > 2500;

SQLite has no DELETE … USING, so deletes still go through a subquery.

SQL Server

-- The alias is updated, and the FROM clause repeats the target table.
UPDATE c
SET c.loyalty_points = c.loyalty_points + 10
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

The alias is what gets updated, and FROM repeats the target table to give that alias a definition. It reads oddly — the table is named twice for one statement — but it is the only form T-SQL accepts, and DELETE follows the same pattern.

Oracle

-- Oracle has no UPDATE ... FROM and no UPDATE ... JOIN.
-- EXISTS is the portable answer, and the one to use.
UPDATE customers c
SET c.loyalty_points = c.loyalty_points + 10
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'refunded'
);

DELETE FROM wishlists w
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.id = w.product_id AND p.stock_quantity = 0
);

Oracle has neither UPDATE … JOIN nor UPDATE … FROM. EXISTS with a correlated subquery is the normal answer, and it is the same statement that runs on the other four engines.

There is a second option, the updatable join view — UPDATE (SELECT a.col, b.col FROM a JOIN b ON …) SET … — but Oracle only accepts it when it can prove the join preserves keys, which requires a unique constraint or primary key on the joined column. When it cannot, it raises ORA-01779, and the fix is to go back to EXISTS.

Before you run any of them

These statements are the reason Planche rewrites UPDATE and DELETE into the matching SELECT before running them: a join makes the affected set much harder to work out by reading than a plain WHERE does. Turn on Settings › Safety › Preview affected rows and you get the count and a sample first. The rewrite understands all five shapes above, and the repository has a script that checks the derived count against the rows the real statement changes on MySQL, PostgreSQL, SQL Server, SQLite and Oracle.