8.5 Multiple-Table UPDATE and DELETE Statements

MySQL 4 supports the use of multiple tables in UPDATE and DELETE statements. 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:

• Name the tables involved in the operation following the UPDATE keyword, 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, to update one table based on another, do this:

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;

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