Unit 7 · Data that lasts
Safe SQL UPDATE: always WHERE id =
An UPDATE changes every row its WHERE matches, and with no WHERE it changes every row in the table. Match on the primary key, WHERE id = 2, so exactly one row changes.
The mix-up
What people often think
“I can find the latte by its old price.”
What the tutor says
Prices can repeat, so a WHERE on price may catch other rows. Use the primary key: WHERE id = 2 matches exactly one row.
How it works
Matching on a value like an old price is risky: prices repeat, so other rows can be caught too. After any update, check the rest of the table, not only the row you meant to change.
Deleting a row and inserting it again is not an edit. The new row gets a new id, and old orders still point at the old one. Change the row where it is.
Side by side
UPDATE menu SET price = 5.25 WHERE price = 4.75;UPDATE menu SET price = 5.25 WHERE id = 2;Close cousins of this mix-up
The tutor watches for these in the same lesson.
“If the latte is right, the update worked.”
Check the rest of the menu too. An UPDATE without a precise WHERE changes every row it matches.
“The safe fix is to delete the latte and add it again.”
Deleting gives the latte a new id, and old orders still point at id 2. Change the row where it is with UPDATE ... WHERE id = 2.
Practise it with a tutor watching
Tables and rows
Read a database table and write SELECT, INSERT and a safe UPDATE in SQL.
Free with an account. The first two units need no account.