UPDATE
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Changing values
UPDATE changes values in rows that already exist: SET says what changes, WHERE says in which rows. Alex earns a badge:
UPDATE trainers
SET badges = 4
WHERE name = 'Alex';
SELECT name, badges FROM trainers;The WHERE works exactly like in a SELECT. Now watch what happens without it:
UPDATE trainers
SET badges = 0;
SELECT name, badges FROM trainers;The missing WHERE
Every trainer lost their badges. An UPDATE without WHERE touches every row of the table, silently. Here the sandbox resets; in a real database there is no undo button.
Exercise 2 — Several columns, computed values
SET accepts several assignments separated by commas, and the new value can be computed from the current one. Taylor's whole team levels up and gets a new capture date:
UPDATE codemon
SET level = level + 1,
capture_date = '2023-04-01'
WHERE trainer_id = 3;
SELECT name, level, capture_date FROM codemon WHERE trainer_id = 3;level = level + 1 reads the current level of each row and stores that value plus one. Any expression works: level * 2, UPPER(name), badges + 1…
Look before you change
Before an UPDATE, run a SELECT with the same WHERE: the rows it returns are exactly the rows that will change.
Exercise 3 — Everyone?
What does UPDATE trainers SET badges = badges + 1; do?
Hint : No WHERE, no filter.
Exercise 4 — Adopting the wild one
Complete the statement so that the wild Plantachu becomes Morgan's Codémon (trainer_id 2).
Hint : Change the column that links a Codémon to its trainer; the name is text.
Exercise 5 — How many rows changed?
An UPDATE does not show the rows it touched. changes() returns how many rows the last statement affected:
UPDATE codemon
SET level = level + 5
WHERE type = 'fire';
SELECT changes() AS rows_changed;Two fire Codémons, two rows updated. Now a classic mistake — a lowercase name:
UPDATE trainers
SET badges = 10
WHERE name = 'alex';
SELECT changes() AS rows_changed;No match is not an error
'alex' is not 'Alex', so the WHERE matched nothing — and SQL happily updated zero rows without complaining. When changes() says 0, check your WHERE.
Exercise 6 — Rows touched
Trainers: Alex (3 badges), Morgan (5), Taylor (2), Jordan (0); Morgan and Jordan live in Azure City. Match each UPDATE with the number of rows it changes.
UPDATE trainers SET badges = 0UPDATE trainers SET badges = 0 WHERE hometown = 'Azure City'UPDATE trainers SET badges = 0 WHERE name = 'Alex'UPDATE trainers SET badges = 0 WHERE badges > 104210Exercise 7 — Back to the wild
What does UPDATE codemon SET trainer_id = NULL WHERE name = 'Firochu'; do?
Hint : NULL is a value you can store, and WHERE keeps the change to one row.
Exercise 8 — Level up
Professor Pi rewards seniority: every Codémon captured before February 2023 gains 2 levels.
Write the UPDATE, then show name, capture_date and level of every Codémon that has a capture date, from the oldest capture to the most recent.
-- Write your statements below, and finish with a SELECT to check the result