Deleting Rows with DELETE

DELETE deletes rows from a table. Ava Green has asked the shop to remove her reviews:

DELETE FROM reviews
WHERE customer_id = 5;

After DELETE FROM comes the table, and WHERE says which rows to delete. Results shows Rows changed: 2, because Ava wrote two reviews, and a query shows that 23 of the 25 reviews are left:

SELECT COUNT(*) AS reviews
FROM reviews;

DELETE always deletes whole rows, so it has no list of columns: DELETE title FROM games is an error, and so is DELETE * FROM games, which you may come across in other databases. To remove just one value from a row, use UPDATE: SET city = NULL.

And just like an UPDATE, a DELETE without WHERE deletes every row: after DELETE FROM reviews;, the table is empty. It still exists, with its columns, but it has no rows. So try out the WHERE in a SELECT first, as you've learned.

Some rows can't be deleted. Try deleting the refunded order 7:

DELETE FROM orders
WHERE id = 7;

Error on line 1: FOREIGN KEY constraint failed

The row of order_items with order 7 points at the order with its foreign key order_id. If the order disappeared, the item would point at an order that doesn't exist. The database protects you from that: it refuses to delete a row that another row points at. Its message doesn't say which row it is, so you have to find it yourself. Think of which tables have a foreign key to this one.

The solution is to delete the rows that point at it first, and then the row itself:

DELETE FROM order_items
WHERE order_id = 7;

DELETE FROM orders
WHERE id = 7;

The order matters: first the rows that point, and last the row they point at. The same protection works for UPDATE too: it can't make a foreign key point at a row that doesn't exist either.