Doing More with MySQL - Learning MySQL - page 290

You can see that the subquery remains the same, but the outer SELECT query is replaced
by a DELETE statement. The DELETE statement syntax is as follows: first, the keyword
DELETE is followed by the table or tables from which rows should be removed; second,
the keyword FROM is followed by the table or tables that should be queried to determine
which rows to delete; and, last, a WHERE clause (and any other query clauses, such as
GROUP BY or HAVING ) follow. In this query, rows are deleted from the track table using
the track table in the query along with the played table in the nested subquery.
As another example, let's clean up our database to remove albums and tracks by the
band New Order:
mysql> DELETE FROM track, album USING artist, album, track WHERE
-> artist_name = "New Order" AND
-> artist.artist_id = album.artist_id AND
-> artist.artist_id = track.artist_id AND
-> album.album_id = track.album_id;
Query OK, 93 rows affected (0.00 sec)
This query deletes rows from track and album , based on a query that involves artist ,
album , and track . You can see the result is that 93 rows are removed: 7 albums and 86
tracks.
In this syntax, the keywords DELETE FROM are followed by the table or tables from which
you want to delete rows. The keyword USING then follows with a list of tables that are
used in the query part of the statement (and then the WHERE clause or other associated
query mechanisms).
With MySQL versions between 4.0 and 4.02, you had to use the following syntax:
mysql> DELETE track, album FROM artist, album, track WHERE
-> artist_name = "New Order" AND
-> artist.artist_id = album.artist_id AND
-> artist.artist_id = track.artist_id AND
-> album.album_id = track.album_id;
Query OK, 93 rows affected (0.10 sec)
The query identifies the artist_id of "New Order" and performs a join between the
tables.
We prefer the newer syntax because it is clearer: DELETE FROM some tables USING other
tables to drive the querying process.
Note that you can use clauses such as LEFT JOIN and INNER JOIN in DELETE statements.
However, you can't delete from a table that's read from in a nested subquery, such as
in the following line:
mysql> DELETE FROM artist WHERE artist_id IN (SELECT artist_id FROM artist);
ERROR 1093 (HY000): You can't specify target table 'artist' for update in
FROM clause
In multiple table deletes, you can't use ORDER BY or LIMIT clauses.