UPDATE and DELETE Without Disasters

Changing rows that already exist

UPDATE rewrites columns on existing rows, DELETE removes rows. Both take the same WHERE clause you know from unit 2, and on both, the WHERE clause is the safety catch:

UPDATE pets SET age = 4 WHERE name = 'Biscuit';

DELETE FROM pets WHERE species = 'cat';

SET lists the changes (SET age = 4, species = 'dog' for several). WHERE decides which rows they hit.

Now the classic disaster: forget the WHERE and the statement applies to every row in the table. UPDATE pets SET age = 99; makes every pet 99. DELETE FROM pets; empties the table. No confirmation, no undo (outside a transaction, unit 10). Every engineer knows a story like this.

The professional habit

Before running an UPDATE or DELETE on data you care about, run a SELECT with the exact same WHERE clause first:

SELECT * FROM pets WHERE species = 'cat';
-- looks right? then:
DELETE FROM pets WHERE species = 'cat';

The SELECT shows you precisely which rows are about to be affected. If it returns 40,000 rows when you expected 3, you just saved yourself. This costs five seconds and becomes automatic with practice.