SQL UPDATE: Change Only Rows That Match the Intended Rule

The practice task

Mark reviewed draft lessons as ready. An unreviewed draft must stay a draft, and an archived lesson must remain archived even if its review flag is set.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE lessons(id INTEGER, status TEXT, reviewed INTEGER);
INSERT INTO lessons VALUES (1,'draft',1),(2,'draft',0),(3,'archived',1);
UPDATE lessons SET status = 'ready'
WHERE status = 'draft' AND reviewed = 1;

SELECT id, status FROM lessons ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Only lesson 1 satisfies both conditions. Lesson 2 is not reviewed, and lesson 3 is not a draft. The unchanged rows are part of the check: seeing one ready row alone would not establish that the rest of the table was preserved.

Before adapting an update, run a SELECT using the same WHERE condition to inspect the intended rows. If the update changes a value used in that condition, remember that a later preview may show no matches because the change has already happened.

The condition expresses the workflow rule; it is not a concurrency policy. A live application may need transactions or additional checks when another process can edit the same records. This small example verifies a deterministic row-selection rule in an isolated database only.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Preview the target population using the same condition.
• Verify both changed and deliberately unchanged records.
• An UPDATE without WHERE targets every row in its table.