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.
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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]