O'Reilly logo

MySQL in a Nutshell by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.'); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required