- 2.1. The Server SQL Mode
- 2.2. MySQL Identifier Syntax and Naming Rules
- 2.3. Case Sensitivity in SQL Statements
- 2.4. Character Set Support
- 2.5. Selecting, Creating, Dropping, and Altering Databases
- 2.6. Creating, Dropping, Indexing, and Altering Tables
- 2.7. Obtaining Database Metadata
- 2.8. Performing Multiple-Table Retrievals with Joins
- 2.9. Performing Multiple-Table Retrievals with Subqueries
- 2.10. Performing Multiple-Table Retrievals with UNION
- 2.11. Multiple-Table Deletes and Updates
- 2.12. Performing Transactions
- 2.13. Foreign Keys and Referential Integrity
- 2.14. Using FULLTEXT Searches
This chapter is from the book
This chapter is from the book
This chapter is from the book
2.11. Multiple-Table Deletes and Updates
Sometimes it’s useful to delete rows based on whether they match or don’t match rows in another table. Similarly, it’s often useful to update rows in one table using the contents of rows 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 Section 2.8, “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, this statement deletes all rows in a table t that have id values greater than 100:
DELETE FROM t WHERE id > 100;
But what if you want to delete rows based not on properties inherent in the rows themselves, but rather on their relationship to rows in another table? Suppose that you want to delete from t those rows with id values that are present in or missing from another table t2?
To write a multiple-table DELETE, name all the tables in a FROM clause and specify the conditions used to match rows in the tables in the WHERE clause. The following statement deletes rows from table t1 where there is a matching id value in table t2:
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
Notice that if a column name appears in more than one of the tables, it is ambiguous and must be qualified with a table name.
The syntax also supports deleting rows 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 INNER JOIN t2 ON t1.id = t2.id;
What if you want to delete nonmatching rows? 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 nonmatching rows. That is, use a LEFT JOIN or RIGHT JOIN. For example, to identify rows in t1 that have no match in t2, 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 rows 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. This syntax uses a FROM clause to list the tables from which rows are to be deleted and a USING clause to join the tables that determine which rows to delete. The preceding multiple-table DELETE statements can be rewritten using this syntax as follows:
DELETE FROM t1 USING t1 INNER JOIN t2 ON t1.id = t2.id; DELETE FROM t1, t2 USING t1 INNER JOIN t2 ON 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, 2012 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 want to add a point to everyone’s score. With a multiple-table UPDATE, you can do this as follows:
UPDATE score, grade_event SET score.score = score.score + 1 WHERE score.event_id = grade_event.event_id AND grade_event.date = '2012-09-23' AND grade_event.category = 'Q';
In this case, you could accomplish the same objective using a single-table update and a subquery:
UPDATE score SET score = score + 1 WHERE event_id = (SELECT event_id FROM grade_event WHERE date = '2012-09-23' AND category = 'Q');
But other updates cannot be written using subqueries. For example, you might want to not only identify rows to update based on the contents of another table, but to copy column values from one table to another. The following statement copies t1.a to t2.a for rows that have a matching id column value:
UPDATE t1, t2 SET t2.a = t1.a WHERE t2.id = t1.id;
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. For details, see Section 2.13, “Foreign Keys and Referential Integrity.”