12.5. Multiple-Table UPDATE and DELETE Statements

12.5. Multiple-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. Such 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. (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.

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; 

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