Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 books
WHERE author_id = @potter;
   
DELETE FROM authors
WHERE author_id = @potter;

In the first part, we use the SET statement to establish a variable called @potter that will contain the results of the SELECT statement that follows in parentheses, another subquery. Incidentally, although this subquery is not available before version 4.1, user-defined variables are. The second SQL statement deletes the rows from books where the author identification number matches the value of the temporary variable. Next, we delete the data from the authors table, still making use of the variable. A user-defined variable will last until it’s reset or until the MySQL session is closed.