3.9. 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 just don't want that kind of business), we could issue the following statement:

DELETE FROM books WHERE author_id =    (SELECT authors.rec_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 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 statement would be to utilize user-defined variables. Here is the same example using variables:

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

In the first stanza, 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.