SQL Pocket Guide, 2nd Edition

Deleting Data

Use the DELETE statement to delete rows from a table:

DELETE
FROM data_source
WHERE predicates

Most often, you’ll write a WHERE clause to identify one or more specific rows to delete. 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;

Note that SQL Server, MySQL, and PostgreSQL do not allow the alias on the target table. See the later section "Predicates" for more details on the different kinds of predicates that you can write.

Deleting in Order (MySQL)

When you delete multiple rows from a table on which a self-referential foreign key constraint is defined, you should include an ORDER BY clause in your DELETE statement 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 later 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 ...