Multi-Table UPDATE and DELETE Statements

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. They can be useful for storage engines where transactions and foreign keys are not supported, and to make sure that the tables are consistent. These statements can be used to perform the
following operations:

• Update rows in one table by transferring information from another table
• Update rows in one table, determining which rows to update by referring to another table
• Update rows in multiple tables with a single statement
• Delete rows from one table, determining which rows to delete by referring to another table
• Delete rows from multiple tables with a single statement

Some of the principles involved in writing joins in SELECT statements also apply to multiple-table UPDATE
and DELETE statements. This section provides a brief overview of their syntax.

A multiple-table UPDATE is an extension of a single-table statement:

• Following the UPDATE keyword, name the tables involved in the operation, separated by commas.
(all the tables used in the query must be named, even if all of them are not being updated.)
• 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.

For example, 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;

Multiple-table DELETE statements can be written in two formats. The following example demonstrates one
syntax, for a query that deletes rows from a table t1 where the id values match those in a table t2:

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

The second syntax is slightly different:

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

To delete the matching records from both tables, the statements are:

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

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