Multiple-Table Deletes and Updates

Sometimes it's useful to delete records based on whether they match or don't match records in another table. Similarly, it's often useful to update records in one table using the contents of records in another table. This section describes how to perform multiple-table DELETE and UPDATE operations. These types of statements draw heavily on the concepts used for joins, so be sure you're familiar with the material discussed earlier in "Performing Multiple-Table Retrievals with Joins."

To perform a single-table DELETE or UPDATE, you refer only to the columns of one table and thus need not qualify the column names with the table name. For example, to delete all records in a table t that have id values greater than 100, you'd write a statement like this:

DELETE FROM t WHERE id > 100;

But what if you want to delete records based not on properties inherent in the records themselves, but rather on their relationship to records in another table? Suppose that you want to delete from t those records with id values that are found in another table t2?

To write a multiple-table DELETE, name all the tables in a FROM clause and specify the conditions used to match up records in the tables in the WHERE clause. The following statement deletes records from table t1 where there is a matching id value in table t2:

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

Notice that the FROM clause names all the tables involved in the operation, just as when writing a join. In addition, if a column name appears in more than one of the tables, it becomes ambiguous and must be qualified with a table name. This too is similar to writing a join.

The syntax also allows for deleting records from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword:

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

What if you want to delete non-matching records? A multiple-table DELETE can use any kind of join that you can write in a SELECT, so employ the same strategy that you'd use when writing a SELECT that identifies the non-matching records. That is, use a LEFT JOIN or RIGHT JOIN. For example, to identify records in t1 that have no match in t2, you'd write a SELECT like this:

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

The analogous DELETE statement to find and remove those records from t1 uses a LEFT JOIN as well:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

MySQL supports a second multiple-table DELETE syntax. With this syntax, use a FROM clause to indicate which tables records are to be deleted from and a USING clause to list the tables that determine which records to delete. The preceding multiple-table DELETE statements can be rewritten using this syntax as follows:

DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

The principles involved in writing multiple-table UPDATE statements are quite similar to those used for DELETE: Name all the tables that participate in the operation and qualify column references as necessary. Suppose that the quiz you gave on September 23, 2004 contained a question that everyone got wrong, and then you discover that the reason for this is that your answer key was incorrect. As a result, you must add a point to everyone's score. Without multiple-table UPDATE capability, you might accomplish this using two statements. First, look up the event ID corresponding to the quiz for the given date:

SELECT @id := event_id FROM grade_event
WHERE date = '2004-09-23' AND category = 'Q';

Then use the ID value to identify the relevant score records:

UPDATE score SET score = score + 1 WHERE event_id = @id;

With a multiple-table UPDATE, you can do the same thing with a single statement:

UPDATE score, grade_event SET score.score = score.score + 1
WHERE score.event_id = grade_event.event_id
AND grade_event.date = '2004-09-23' AND grade_event.category = 'Q';

You can not only identify records to update based on the contents of another table, you can copy column values from one table to another. The following statement copies t1.a to t2.a for records that have a matching id column value:

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

If you want to perform multiple-table deletes or updates for InnoDB tables, you need not use the syntax just described. Instead set up a foreign key relationship between tables that includes an ON DELETE CASCADE or ON UPDATE CASCADE constraint. See "Foreign Keys and Referential Integrity" for details.