-> (SELECT * FROM played WHERE track.artist_id = played.artist_id AND

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 cleanup 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 seethe 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

Download 4.24 Mb.

Share with your friends: