Chapter 10 · Changing Data Safely
UPDATE with Precise Predicates
UPDATE is powerful because one statement can change one row or millions. Precision comes from proving the target set before changing it, then verifying the result inside a controlled transaction.
Learning outcomes
An UPDATE identifies existing rows with a predicate and computes replacement values from expressions. The critical design question is not “what value should change?” but “which exact rows are authorized to change?”
Prove an UPDATE target set with a matching SELECT.
Use expressions, CASE, and related data to compute new values.
Protect updates with keys, expected old values, and status predicates.
Inspect affected rows with RETURNING and row-count checks.
Recognize non-deterministic source matches in UPDATE ... FROM patterns.
The preview–change–verify pattern
Use the same predicate in the preview and change. Do not improvise the WHERE clause after review.
Start with a primary-key update
SELECT customer_id, status, loyalty_pointsFROM customerWHERE customer_id = 3;UPDATE customerSET status = 'active', loyalty_points = loyalty_points + 20, updated_at = CURRENT_TIMESTAMPWHERE customer_id = 3RETURNING customer_id, status, loyalty_points, updated_at;The primary key limits identity, while the expressions compute new values from the old row. A key predicate is necessary but not always sufficient when concurrent state matters.
Optimistic precision includes expected state
UPDATE sales_orderSET status = 'paid', total_amount = 69.00WHERE order_id = 101 AND status = 'draft' AND total_amount = 0RETURNING order_id, status, total_amount;If another transaction changed the order first, this statement affects zero rows. The application must treat that as a state conflict, not as success.
Set-based updates with CASE
UPDATE customerSET loyalty_points = loyalty_points + CASE WHEN status = 'active' AND loyalty_points < 50 THEN 15 WHEN status = 'active' THEN 5 ELSE 0 END, updated_at = CURRENT_TIMESTAMPWHERE status = 'active'RETURNING customer_id, loyalty_points;A single set-based statement is easier to make atomic than a client loop. Keep the policy readable and ensure every branch has an intentional result.
Update values from related data
UPDATE product AS pSET product_name = f.product_name, unit_price = f.unit_price, stock_qty = f.stock_qty, updated_at = f.source_timeFROM product_feed AS fWHERE p.sku = f.sku AND ( p.product_name <> f.product_name OR p.unit_price <> f.unit_price OR p.stock_qty <> f.stock_qty )RETURNING product_id, sku, unit_price, stock_qty;If several source rows match one target row, the chosen source row may be arbitrary in SQLite. Enforce one source row per target key before running the update.
Correlated subquery alternative
UPDATE productSET unit_price = ( SELECT f.unit_price FROM product_feed AS f WHERE f.sku = product.sku)WHERE EXISTS ( SELECT 1 FROM product_feed AS f WHERE f.sku = product.sku AND f.unit_price <> product.unit_price);The scalar subquery must produce at most one value per target row. A unique source key makes that cardinality explicit.
Mass-update guardrails
| Guardrail | Purpose |
|---|---|
| Matching SELECT | Makes the proposed target set reviewable. |
| Key or bounded predicate | Prevents accidental whole-table updates. |
| Expected old value | Detects stale assumptions and concurrent changes. |
| RETURNING / OUTPUT | Shows the rows actually affected. |
| Affected-row assertion | Stops the workflow when the count differs from expectation. |
| Transaction and rollback path | Keeps verification inside the decision boundary. |
Reusable Chapter 10 practice database
Run this setup once in a disposable SQLite database. Every lesson uses the same constrained tables so that write behavior, references, conflicts, and verification can be compared consistently.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS change_request;DROP TABLE IF EXISTS order_archive;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product_feed;DROP TABLE IF EXISTS customer_stage;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')), loyalty_points INTEGER NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0), deleted_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0), discontinued_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'paid', 'cancelled', 'refunded')), total_amount REAL NOT NULL DEFAULT 0 CHECK (total_amount >= 0), ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price REAL NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)) STRICT;CREATE TABLE customer_stage ( email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'closed'))) STRICT;CREATE TABLE product_feed ( sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), source_time TEXT NOT NULL) STRICT;CREATE TABLE order_archive ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount REAL NOT NULL, ordered_at TEXT NOT NULL, archived_at TEXT NOT NULL) STRICT;CREATE TABLE change_request ( request_key TEXT PRIMARY KEY, operation TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO customer (customer_id, email, full_name, status, loyalty_points, updated_at)VALUES (1, 'nadia@example.com', 'Nadia Rahimi', 'active', 120, '2026-08-01 09:00:00'), (2, 'omar@example.com', 'Omar Haddad', 'active', 80, '2026-08-01 09:00:00'), (3, 'lina@example.com', 'Lina Chen', 'paused', 40, '2026-08-01 09:00:00'), (4, 'ava@example.com', 'Ava Morgan', 'active', 0, '2026-08-01 09:00:00'), (5, 'noah@example.com', 'Noah Silva', 'closed', 15, '2026-08-01 09:00:00');INSERT INTO product (product_id, sku, product_name, unit_price, stock_qty, updated_at)VALUES (10, 'SQL-FOUND', 'Database Foundations', 49.00, 35, '2026-08-01 09:00:00'), (11, 'SQL-QUERY', 'SQL Query Practice', 69.00, 18, '2026-08-01 09:00:00'), (12, 'SQL-CARD', 'SQL Reference Card', 15.00, 80, '2026-08-01 09:00:00'), (13, 'SQL-LAB', 'SQLite Lab Bundle', 29.00, 22, '2026-08-01 09:00:00'), (14, 'DQ-WORK', 'Data Quality Workbook', 24.50, 0, '2026-08-01 09:00:00');INSERT INTO sales_order (order_id, external_ref, customer_id, status, total_amount, ordered_at)VALUES (100, 'WEB-100', 1, 'paid', 79.00, '2026-07-01 10:00:00'), (101, 'WEB-101', 2, 'draft', 0.00, '2026-07-10 11:00:00'), (102, 'WEB-102', 3, 'cancelled', 29.00, '2025-12-01 12:00:00'), (103, 'WEB-103', 4, 'paid', 69.00, '2026-08-01 13:00:00'), (104, 'WEB-104', 5, 'refunded', 15.00, '2025-11-15 14:00:00');INSERT INTO order_item VALUES (100, 1, 10, 1, 49.00), (100, 2, 13, 1, 30.00), (102, 1, 13, 1, 29.00), (103, 1, 11, 1, 69.00), (104, 1, 12, 1, 15.00);INSERT INTO customer_stage VALUES ('marta@example.com', 'Marta Costa', 'active'), ('reza@example.com', 'Reza Nouri', 'active'), ('lina@example.com', 'Lina Chen', 'active');INSERT INTO product_feed VALUES ('SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00'), ('SQL-CARD', 'SQL Reference Card', 15.00, 75, '2026-08-05 06:00:00'), ('SQL-OPS', 'Database Operations', 89.00, 12, '2026-08-05 06:00:00');The setup drops tables. Never execute training reset scripts against a production database.
Practice lab
- Activate Lina only if her current status is paused.
- Add five points to active customers with fewer than 100 points.
- Refresh existing products from product_feed.
- Try an UPDATE without WHERE only in a disposable transaction, inspect it, and roll it back.
- Write an assertion for an update expected to affect exactly one row.
BEGIN;UPDATE customerSET status = 'paused';SELECT changes() AS rows_changed;SELECT customer_id, statusFROM customerORDER BY customer_id;ROLLBACK;Checkpoint
Control the target set
- Why should SELECT and UPDATE share the same predicate?
- What does a zero-row optimistic update mean?
- Why is a client-side row loop usually weaker than one set-based UPDATE?
- What assumption must UPDATE ... FROM make about source rows?
- When should COMMIT occur?
Review the answers
The shared predicate makes review meaningful. Zero rows can mean the expected state no longer exists. A set-based update is easier to make atomic and verify. Each target needs at most one source match. Commit only after row counts and invariants pass.
Summary and references
- Preview the exact target set before changing it.
- Use current-state predicates when stale writes matter.
- Set-based expressions and CASE encode policies atomically.
- RETURNING and row-count checks convert assumptions into evidence.