MySQL in a Nutshell, 2nd Edition

Deleting Data

To delete specific rows of data, you can use the DELETE statement. For example, if we want to delete all rows of data from our books table for the author J. K. Rowling, because we’ve decided not to carry Harry Potter books, we could issue the following statement:

DELETE FROM books
WHERE author_id =
   (SELECT authors.author_id FROM authors
    WHERE author_last = 'Rowling' 
    AND author_first = 'J. K.');
   
DELETE FROM authors
WHERE author_last = 'Rowling' 
AND author_first = 'J. K.';

Here, we’re deleting only rows from the books table where the author identification number is whatever is selected from the authors table based on the specified author’s last name and first name. That is to say, the author_id must be whatever value is returned by the SELECT statement, the subquery contained in the parentheses. This statement involves a subquery, so it requires version 4.1 or later of MySQL. To delete these same rows with an earlier version of MySQL, you would need to run the SELECT statement shown in parentheses here separately (not as a subquery), make note of the author’s identification number, and then run the first DELETE statement, manually entering the identification number at the end instead of the parenthetical SELECT statement shown.

An alternative to the previous SQL statements would be to utilize user-defined variables. Here is the same example using variables:

SET @potter = (SELECT author_id FROM authors WHERE author_last = 'Rowling' AND author_first = 'J. K.'); DELETE FROM ...