MySQL 5.0 Certification Study Guide
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:
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:
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. |