Deleting Data
Use the DELETE statement to delete rows from a table:
DELETE FROMdata_source
WHEREpredicates
For example, you may want to delete states for which you don’t know the population:
DELETE FROM state s WHERE s.population IS NULL;
SQL Server, MySQL, and PostgreSQL 8.1 and earlier do not allow the alias on the target table. See the section Predicates for more details on the different kinds of predicates that you can write.
Deleting in Order
MySQL requires that you include an ORDER BY clause in your DELETE statement when deleting multiple rows from a table having a self-referential foreign-key constraint. This is to ensure that child rows are deleted before their parents. Because MySQL checks for constraint violations during statement execution, this is a MySQL-only issue.
Note
The ISO SQL standard allows constraint checking to be done either at the end of each statement’s execution or at the end of a transaction, but never during statement execution.
In the section Subquery Inserts, you will
find an INSERT INTO…SELECT FROM
statement that creates a new tour in the trip
table called J's Tour
. If you wish to delete J's Tour
, you must issue a statement such
as:
DELETE FROM trip WHERE name = 'J''s Tour' ORDER BY CASE stop WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 6 THEN 3 WHEN 4 THEN 4 WHEN 3 THEN 5 WHEN 5 THEN 6 END DESC;
The CASE expression in this statement’s ORDER BY clause hardcodes a child-first delete order. Obviously, this completely defeats the purpose of a multirow DELETE statement. If ...
Get SQL Pocket Guide, 3rd 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.