DELETE
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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?
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.
DELETE FROM codemon WHERE type = 'fire'DELETE FROM codemon WHERE level > 20DELETE FROM codemonDELETE FROM codemon WHERE trainer_id = 32083Exercise 7 — The safest first step
You must delete the Codémons below level 10. What should you do first?
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.
-- Write your statements below, and finish with a SELECT to check the result