SQL Pocket Guide, 3rd Edition

Deleting Data

Use the DELETE statement to delete rows from a table:

DELETE
FROM data_source
WHERE predicates

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 ...