Learn SQL: Removing rows with DELETE

Tidying up

St Nicholas' Day is over, strawberries are out of season and three customers cancelled their order. "That clutter can go," says Jens. Careful: what you delete is gone.

Removing rows

DELETE FROM removes rows from a table. With WHERE you choose which ones. You don't name any columns: you always remove whole rows.

Here the St Nicholas bun disappears from the web shop. Below you still see the deleted row for a moment, crossed out.

DELETE FROM products WHERE name = 'St Nicholas bun'

Look first, then delete

A DELETE without WHERE empties the whole table. That's why good analysts work like this: first a SELECT * with the same WHERE, and only when it shows exactly the right rows do you replace SELECT * with DELETE.

Below is the test for products that are no longer active. Two rows: that's right.

SELECT * FROM products WHERE active = 0

Result
id	name	category	price	stock	gluten_free	active
11	Strawberry tart	cake	18.5	2	0	0
17	St Nicholas bun	seasonal	3.5	3	0	0

Orphan rows

Every order has lines in order_lines, which point to the order through order_id. Delete only the order and its lines stay behind. They point to an order that no longer exists: orphan rows.

Here order 7 is deleted. The query below looks for lines without an order, and now finds one.

Many databases even refuse a DELETE like that. So delete the lines first and the order after.

DELETE FROM orders WHERE id = 7

Try it yourself

Delete all products that are no longer active.

Ready for more?

The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson