MySQL - Multiple Table UPDATE and DELETE

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. Such statements can be used to perform the following operations:

  1. Update rows in one table by transferring information from another table.
  2. Update / delete rows in one table, determining which rows to update / delete by referring to another table.
  3. Update / delete rows in multiple tables with a single statement.

Follow the UPDATE keyword with names of all tables involved in the operation, separated by commas. You must name all the tables used in the query, even if you aren't updating all of them. In the WHERE clause, describe the conditions that determine how to match records in the tables. In the SET clause, assign values to the columns to be updated. These assignments can refer to columns from any of the joined tables.

This statement identifies matching records in two tables based on id values, and then copies the name column from t2 to t1:

UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.id;

Multi-table DELETE statements can be written in two formats:

DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

To delete matching records from both tables:

DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;

The ORDER BY and LIMIT clauses normally supported by UPDATE and DELETE aren't allowed when these statements are used for multiple-table operations.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License