- MySQL Naming Rules
- Selecting, Creating, Dropping, and Altering Databases
- Creating, Dropping, Indexing, and Altering Tables
- Getting Information about Databases and Tables
- Retrieving Records from Multiple Tables
- Multiple-Table Deletes and Updates
- Performing Transactions
- Foreign Keys and Referential Integrity
- Using FULLTEXT Searches
- Writing Comments
- Features That MySQL Does Not Support
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Multiple-Table Deletes and Updates
Prior to MySQL 4, one limitation of DELETE is that you can refer only to columns of the table from which you're deleting records. But sometimes it's useful to delete records based on whether they match or don't match records in another table. This capability has been added in MySQL 4.0.0. Similarly, it's often useful to update records in one table using the contents of records in another table, a feature introduced in MySQL 4.0.2. 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 the "Retrieving Records from Multiple Tables" section.
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? For example, suppose 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? 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 as follows:
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;
A somewhat different multiple-table DELETE syntax is supported as of MySQL 4.0.2. 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;
Another type of multiple-table DELETE than is described here can be achieved by setting up a foreign key relationship between tables that includes an ON DELETE CASCADE constraint. See the "Foreign Keys and Referential Integrity" section later in this chapter for details.
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, 2002 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 event WHERE date = '2002-09-23' AND type = 'Q';
Then use the ID 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, event SET score.score = score.score + 1 WHERE score.event_id = event.event_id AND event.date = '2002-09-23' AND event.type = 'Q';
You not only can 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;