Learning MySQL

Updates and Deletes with Multiple Tables

In Chapter 5, we showed you how to update and delete data. In the examples there, each update and delete affected one table and used properties of that table to decide what to modify. This section shows you more complex updates and deletes, with which you can delete or update rows from more than one table in one statement and can use those or other tables to decide what rows to change.

Deletion

Imagine you’ve just run out of disk space or you’re sick of browsing unwanted data in your music collection. One way to solve this problem is to remove some data, and it’d make sense to remove tracks you’ve never listened to. Unfortunately, this means you need to remove data from the track table using information from the played table.

With the techniques we’ve described so far in the book, there’s no way of doing this without creating a table that combines the two tables (perhaps using INSERT with SELECT), removing unwanted rows, and copying the data back to its source. In fact, this is exactly what you had to do prior to MySQL 4.0. This section shows you how you can perform this procedure and other more advanced types of deletion in recent versions of MySQL.

Consider the query you need to write to find tracks you’ve never played. One way to do it is to use a nested query—following the techniques we showed you in Chapter 7—with the NOT EXISTS clause. Here’s the query:

mysql> SELECT track_name FROM track WHERE NOT EXISTS
 -> (SELECT * FROM played WHERE ...