SQL chapters

Chapter 8 of 17

Set-based updates

Try this in our SQLite Compiler →

The idea

An UPDATE changes all rows matching its WHERE clause; an ordinary SQL loop is unnecessary. Omitting WHERE changes every row, so preview the matching records before editing real data. A query returning rows is not an array iteration in a procedural language.

Worked example

DROP TABLE IF EXISTS scores;
CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 80), ('Ravi', 60);
UPDATE scores SET score = score + 5 WHERE score < 70;
SELECT * FROM scores ORDER BY name;

What to check

Asha stays at 80 and Ravi becomes 65. The statement applies to the selected set of rows once.

Practice task

Change only the score for Ravi using a WHERE condition. Before updating, SELECT the rows that match that condition.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on