Managing & Using MySQL, 2nd Edition

Deletes

Deleting data is a straightforward operation. You simply specify the table followed by a WHERE clause that identifies the rows you want to delete:

DELETE FROM table_name [WHERE clause]

As with other commands that accept a WHERE clause, the WHERE clause is optional. If you omit it, you will delete all of the records in the table! Of all the destructive commands in SQL, this is the easiest one to issue by mistake.

MySQL 4.0 has introduced a new, dangerous form of DELETE that supports the ability to delete from multiple tables with a single command:

DELETE table1, table2, ..., tablen
FROM table1, table2, ... tablen
[WHERE clause]

The FROM clause in this syntax does not mean the same thing as it does in the simpler form. In other words, it does not list the tables from which rows are deleted—it lists the tables referenced in the WHERE clause. If you are familiar with the SELECT statement, it works exactly the same as the FROM clause in SELECT statements. The tables you are deleting from are listed directly after the DELETE statement:

DELETE Author, Address
FROM Author, Book, Address
WHERE Author.author_id = Addess.address_id
AND Author.author_id = Book.author_id
AND Book.publish_date < 1980;

This statement deletes all the authors and any address information you have for those authors in the Address table for every author with books published before 1980. The old books will remain in the Book table, because Book was not named after the DELETE keyword. We further cover the complexities ...