Complicated delete conditions

The WHERE clause in a DELETE statement can be almost as complicated as the one in a SELECT statement. It can contain multiple conditions that are connected by AND and OR, and it might contain subqueries.

Suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be re-entered. You know that these rows, unlike the correct ones, have no matching rows in the manufact table. The fact that these incorrect rows have no matching rows in the manufact table allows you to write a DELETE statement such as the one in the following example:

DELETE FROM stock
   WHERE 0 = (SELECT COUNT(*) FROM manufact
        WHERE manufact.manu_code = stock.manu_code);

The subquery counts the number of rows of manufact that match; the count is 1 for a correct row of stock and 0 for an incorrect one. The latter rows are chosen for deletion.

Tip: One way to develop a DELETE statement with a complicated condition is to first develop a SELECT statement that returns precisely the rows to be deleted. Write it as SELECT *; when it returns the desired set of rows, change SELECT * to read DELETE and execute it once more.

The WHERE clause of a DELETE statement cannot use a subquery that tests the same table. That is, when you delete from stock, you cannot use a subquery in the WHERE clause that also selects from stock.

The key to this rule is in the FROM clause. If a table is named in the FROM clause of a DELETE statement, it cannot also appear in the FROM clause of a subquery of the DELETE statement.