UPDATE

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

▶ Do the exercises

Exercise 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?

Options
• Every trainer gains one badge: without WHERE, all rows change
• Only the first trainer gains a badge
• An error: WHERE is required in an UPDATE
• Nothing: badges + 1 is not a valid value

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.

Items to match
• UPDATE trainers SET badges = 0
• UPDATE trainers SET badges = 0 WHERE hometown = 'Azure City'
• UPDATE trainers SET badges = 0 WHERE name = 'Alex'
• UPDATE trainers SET badges = 0 WHERE badges > 10
Definitions
• 4
• 2
• 1
• 0

Exercise 7 — Back to the wild

What does UPDATE codemon SET trainer_id = NULL WHERE name = 'Firochu'; do?

Options
• Firochu becomes wild: its trainer_id is cleared
• An error: a column cannot be set to NULL
• Firochu is deleted
• Every Codémon loses its trainer

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.

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