Free SQL queries lessonsSQL queries · Stage 5 · Changing data · Step 2

Change rows with UPDATE

Change columns of the rows that match, and never forget the WHERE. About 8 minutes.

UPDATE … SET … WHERE

UPDATE changes rows that are already there. Name the table, say what to set with SET, and say which rows with WHERE. SET can use the column's old value, so price = price + 5 raises the price by 5.

You can set several columns at once, separated by commas. The WHERE works exactly like in SELECT: only the rows that pass are changed.

UPDATE t SET c = v WHERE … only the matching rows change.

In code

UPDATE items
SET price = price + 5,
    category = 'Sale'
WHERE category = 'Stationery';

Raise stationery by 5

items

id	name	category	price
1	Pen	Stationery	10
2	Kettle	Kitchen	800
3	Ruler	Stationery	20

Pen is Stationery: WHERE passes, so its price becomes 10 + 5 = 15.

Move 1 of 3

The missing WHERE

Leave the WHERE off and every row in the table is updated. There is no warning and no undo, unless you opened a transaction first. It's one of the most common ways to damage data.

A safe habit: write the WHERE first, run it in a SELECT, look at the rows and count them, and only then turn it into an UPDATE. Do the change inside BEGIN, so a wrong count can be rolled back.

UPDATE without WHERE: changes every row in the table.

In code

SELECT COUNT(*) FROM items
WHERE category = 'Stationery';

UPDATE items SET price = price + 5
WHERE category = 'Stationery';

Quick check

A table has 500 rows. You run UPDATE items SET price = 0 with no WHERE. How many rows get price 0?

AAll 500
BNone, SQL refuses to run it
CJust the first row

Show the answer

All 500. With no WHERE every row matches, so every row is updated.

Your problem

Mark the overdue loans

The loans table has one row per book loan, with the day it is due and a status of open, returned or overdue. Today is day 100. Change the status to overdue for every loan that is still open and was due before day 100.

Tables

loans(id, book, due_day, status)

Up to 2,000 loans

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.