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.'); ...
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.