MULTI-TABLE UPDATE / DELETEDML
Buy Me A Coffee

Modifying or deleting rows in one table based on a join condition with one or more other tables in a single DML statement.

PostgreSQL and SQL Server UPDATE ... FROM syntax allows joining additional tables in an UPDATE. More flexible than standard SQL correlated subquery approach for multi-table updates.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 4.0 Multi-table UPDATE and DELETE with explicit JOIN syntax are native. Only the target table's rows are modified; joining tables are read-only in the statement.
PostgreSQL ✓ Supported 7.2 Uses UPDATE ... FROM and DELETE ... USING syntax. The target table appears once; joined tables are in the FROM/USING clause.
SQL Server ✓ Supported 2000 UPDATE ... FROM and DELETE with FROM clause. Can also use MERGE for upsert-style multi-table DML. The aliased target table in FROM is the idiomatic form.
Oracle ✓ Supported 9i Oracle does not support UPDATE...JOIN directly. Use a correlated subquery, a CTE with MERGE, or update an inline view (if the view is key-preserved).
SQLite ✓ Supported 3.33.0 UPDATE FROM syntax added in 3.33.0 (2020). DELETE with correlated subquery has always been available; DELETE...FROM is not supported — use WHERE EXISTS or WHERE id IN (subquery).

Details

This is one of the more fragmented areas across engines. MySQL's explicit JOIN in UPDATE/DELETE is intuitive but non-standard. PostgreSQL and SQL Server both use FROM/USING clauses, but differ in where the target table appears. Oracle is the most restrictive: no JOIN in UPDATE at all — correlated subqueries or the inline-view trick are the workarounds, and the inline-view approach requires the join to be key-preserved (Oracle must be able to prove which base-table row to update). SQLite gained UPDATE FROM relatively recently (3.33.0); scripts targeting older SQLite deployments need the subquery form. Cross-engine portability is best achieved with correlated subqueries (UPDATE t SET col = (SELECT ...) WHERE EXISTS (...)), which work on all five engines.

Standard Syntax

-- MySQL style UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.col = t2.val WHERE t2.flag = 1;

Version Support

MySQL: Since 4.0 PostgreSQL: Since 7.2 SQL Server: Since 2000 Oracle: Since 9i SQLite: Since 3.33.0

Per-Database Syntax & Notes

MySQL

Multi-table UPDATE and DELETE with explicit JOIN syntax are native. Only the target table's rows are modified; joining tables are read-only in the statement.

UPDATE orders o JOIN customers c ON o.customer_id = c.id SET o.priority = 'high' WHERE c.tier = 'gold'; DELETE o FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.active = 0;

PostgreSQL

Uses UPDATE ... FROM and DELETE ... USING syntax. The target table appears once; joined tables are in the FROM/USING clause.

UPDATE orders o SET priority = 'high' FROM customers c WHERE o.customer_id = c.id AND c.tier = 'gold'; DELETE FROM orders o USING customers c WHERE o.customer_id = c.id AND c.active = false;

SQL Server

UPDATE ... FROM and DELETE with FROM clause. Can also use MERGE for upsert-style multi-table DML. The aliased target table in FROM is the idiomatic form.

UPDATE o SET o.priority = 'high' FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.tier = 'gold'; DELETE o FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.active = 0;

Oracle

Oracle does not support UPDATE...JOIN directly. Use a correlated subquery, a CTE with MERGE, or update an inline view (if the view is key-preserved).

-- Correlated subquery: UPDATE orders o SET o.priority = 'high' WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.tier = 'gold' ); -- Key-preserved inline view: UPDATE ( SELECT o.priority, c.tier FROM orders o JOIN customers c ON o.customer_id = c.id ) v SET v.priority = 'high' WHERE v.tier = 'gold';

SQLite

UPDATE FROM syntax added in 3.33.0 (2020). DELETE with correlated subquery has always been available; DELETE...FROM is not supported — use WHERE EXISTS or WHERE id IN (subquery).

-- UPDATE FROM (3.33.0+): UPDATE orders SET priority = 'high' FROM customers WHERE orders.customer_id = customers.id AND customers.tier = 'gold'; -- DELETE via subquery: DELETE FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE active = 0 );