Deleting rows

DELETE removes the rows that match a WHERE condition:

DELETE FROM product WHERE code = 'P03';

Only the matching rows are removed; the table and its other rows stay.

Like UPDATE, a DELETE with no WHERE hits every row:

DELETE FROM product;   -- empties the whole table!

Always check your WHERE before running a DELETE. The checker runs your DELETE, then reads the table back to confirm the right row is gone.

Question. Cara (customer 3) closes the account. Remove Cara and Cara's orders.

Answer. Each of Cara's orders refers to customer 3 through the foreign key customer_id. Deleting the customer first would leave orders that point at nobody, which breaks referential integrity. So remove the orders first:

DELETE FROM orders WHERE customer_id = 3;

and then the customer:

DELETE FROM customer WHERE id = 3;

Orders 12 and 13 are gone; orders 10 and 11 (Ada's) are still there.

• There is no * in DELETE. DELETE * FROM product is a syntax error; write DELETE FROM product WHERE ….
• DELETE removes rows, DROP TABLE removes the table. After DELETE FROM product the empty table is still there.
• Test a risky delete first as a SELECT with the same WHERE, and check which rows it shows.