DELETE

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Removing rows

DELETE FROM removes rows. There is no column list — a row goes entirely — and the WHERE chooses which ones:

DELETE FROM codemon WHERE name = 'Firochu'; SELECT name FROM codemon ORDER BY codemon_id;

Seven Codémons left. And without WHERE:

DELETE FROM codemon; SELECT COUNT(*) AS remaining FROM codemon;

Empty, but still there

DELETE without WHERE removes every row, but the table itself survives: its columns are intact and you can insert again.

Exercise 2 — Preview, then delete

A DELETE cannot be undone, so look first. The SELECT below uses the WHERE you intend to delete with:

SELECT name, level FROM codemon WHERE level < 10;

Two rows, as expected. Same WHERE, now in the DELETE:

DELETE FROM codemon WHERE level < 10; SELECT name, level FROM codemon ORDER BY level;

All Codemon under level 10 have been removed now!

The habit that saves data

Write the SELECT, check the rows, then replace SELECT … by DELETE and keep the WHERE untouched. It takes ten seconds and avoids the worst kind of mistake.

Exercise 3 — Table or rows?

What does DELETE FROM trainers; do?

Options
• Removes every row; the table itself stays, empty
• Removes the table and its columns
• An error: WHERE is required
• Removes only the first row

Hint : DELETE works on rows, never on the structure.

Exercise 4 — Release the wild ones

Complete the statement so that it removes the Codémons that have no trainer.

Hint : No trainer means a NULL trainer_id, and NULL is tested with IS.

Exercise 5 — Deleting a row that others point to

Jordan owns no Codémon, so removing Jordan is harmless:

DELETE FROM trainers WHERE name = 'Jordan'; SELECT name FROM trainers;

Alex is different: Aquachu and Voltachu carry trainer_id = 1. Delete Alex alone and they point to a trainer who no longer exists — orphan rows. The safe order is children first, then the parent:

DELETE FROM codemon WHERE trainer_id = 1; DELETE FROM trainers WHERE trainer_id = 1; SELECT 'trainers' AS tbl, COUNT(*) AS rows_left FROM trainers UNION ALL SELECT 'codemon', COUNT(*) FROM codemon;

The database can refuse orphans

A table can declare that codemon.trainer_id must match an existing trainer — a foreign key. With that rule enforced, deleting Alex before Alex's Codémons is rejected.

Exercise 6 — How many rows go?

8 Codémons: 2 fire, 3 owned by Taylor (trainer_id 3), levels from 8 to 15. Match each DELETE with the number of rows it removes.

Items to match
• DELETE FROM codemon WHERE type = 'fire'
• DELETE FROM codemon WHERE level > 20
• DELETE FROM codemon
• DELETE FROM codemon WHERE trainer_id = 3
Definitions
• 2
• 0
• 8
• 3

Exercise 7 — The safest first step

You must delete the Codémons below level 10. What should you do first?

Options
• Run SELECT … FROM codemon WHERE level < 10 to see exactly which rows will go
• Run DELETE FROM codemon, then insert the good rows again
• Run the DELETE without WHERE, then check
• Nothing: DELETE asks for confirmation

Hint : Same WHERE, SELECT first.

Exercise 8 — Spring cleaning

Taylor (trainer_id 3) retires from the league.

Delete Taylor's Codémons, then Taylor. Finish with a query showing each remaining trainer's name and codemon_count (0 for Jordan), sorted by name.

Starter code
-- Write your statements below, and finish with a SELECT to check the result
Back to the course