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.
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
track table using information from the
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
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:
SELECT track_name FROM track WHERE NOT EXISTS
-> (SELECT * FROM played WHERE ...