Deletes
Deleting
data
is a straightforward operation. You simply specify the table followed
by a WHERE
clause that identifies the rows you
want to delete:
DELETE FROMtable_name
[WHEREclause
]
As with other commands that accept a WHERE
clause,
the WHERE
clause is optional. If you omit it, you
will delete all of the records in the table! Of all the destructive
commands in SQL, this is the easiest one to issue by mistake.
MySQL 4.0 has introduced a new, dangerous form of
DELETE
that supports the ability to delete from
multiple tables with a single command:
DELETE table1, table2, ..., tablen FROM table1, table2, ... tablen [WHERE clause]
The FROM
clause in this syntax does not mean the
same thing as it does in the simpler form. In other words, it does
not list the tables from which rows are deleted—it lists the
tables referenced in the WHERE
clause. If you are
familiar with the SELECT
statement, it works
exactly the same as the FROM
clause in
SELECT
statements. The tables you are deleting
from are listed directly after the DELETE
statement:
DELETE Author, Address FROM Author, Book, Address WHERE Author.author_id = Addess.address_id AND Author.author_id = Book.author_id AND Book.publish_date < 1980;
This statement deletes all the authors and any address information
you have for those authors in the Address
table
for every author with books published before 1980. The old books will
remain in the Book
table, because
Book
was not named after the
DELETE
keyword. We further cover the complexities ...
Get Managing & Using MySQL, 2nd Edition 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.