Chapter 06 · SQLite Data Modification: INSERT, UPDATE, DELETE, UPSERT, and RETURNING
UPDATE Correctly: Predicates, Expressions, and Avoiding Accidental Wide Changes
Use a verify-first transaction workflow for UPDATE, understand SQLite expressions and UPDATE FROM, and apply optimistic concurrency without accidental wide writes.
Learning outcomes
An UPDATE is safe only when its target set is understood. SQLite will happily update every row if WHERE is omitted, so the professional habit is to make the target observable before making the change durable.
Use SELECT → BEGIN → UPDATE → changes() → verify → COMMIT/ROLLBACK as a repeatable safety workflow.
Use current-row expressions and multi-column assignments correctly.
Understand SQLite UPDATE ... FROM and its portability/multi-match caveat.
Reason about NULL and affinity during updates.
Implement a practical optimistic-concurrency predicate.
Recover a deliberately wide UPDATE with ROLLBACK in a disposable database.
The verify-first update workflow
The WHERE clause is not decoration; it defines the write scope. Before changing data, run the same predicate as a SELECT and confirm identifiers, row count, and current values. Then keep the mutation inside a transaction until post-update verification passes.
DROP TABLE IF EXISTS device_update;CREATE TABLE device_update( device_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, status TEXT NOT NULL, service_count INTEGER NOT NULL DEFAULT 0, version INTEGER NOT NULL DEFAULT 1);INSERT INTO device_update(code,status) VALUES('PUMP-007','active'),('FAN-014','active'),('SENS-003','retired');-- 1. Preview exactly the same predicate.SELECT device_id, code, status FROM device_update WHERE code='FAN-014';-- 2. Protect the change.BEGIN;UPDATE device_update SET status='maintenance' WHERE code='FAN-014';SELECT changes() AS rows_updated; -- expect 1SELECT device_id, code, status FROM device_update WHERE code='FAN-014';COMMIT;If changes() is not the expected number, rollback and investigate. A row count is evidence about scope, not proof that the business operation was valid.
Expressions see the current row
Assignments can use current values. SQLite evaluates the right-hand expressions from the row being updated and then applies the new values. Multi-column assignments can make related state transitions clearer.
UPDATE device_updateSET service_count = service_count + 1, version = version + 1, status = 'active'WHERE code = 'FAN-014';SELECT code, service_count, version, statusFROM device_update WHERE code='FAN-014';Do not read a value in the application, increment it, and write it back unless the workflow also handles concurrent changes. Let SQL express row-local arithmetic when possible.
NULL and type affinity still apply on UPDATE
UPDATE writes values through the same type-affinity and constraint machinery as INSERT. Setting a NOT NULL column to NULL fails; writing numeric-looking text to an INTEGER-affinity column may convert it if conversion is lossless under the table's typing rules.
UPDATE device_update SET service_count='12' WHERE code='PUMP-007';SELECT service_count, typeof(service_count)FROM device_update WHERE code='PUMP-007';-- Fails because status is NOT NULL:UPDATE device_update SET status=NULL WHERE code='PUMP-007';Do not depend on accidental coercions at an API boundary. Bind values using the intended host-language type and validate the data contract.
UPDATE ... FROM: useful, SQLite-specific, and not portable SQL
SQLite supports UPDATE ... FROM beginning with 3.33.0. It lets another table or subquery drive the target update. PostgreSQL and SQL Server have similar-looking syntax, but the construct is not standardized and products differ.
DROP TABLE IF EXISTS service_delta;CREATE TABLE service_delta(code TEXT PRIMARY KEY, add_count INTEGER NOT NULL);INSERT INTO service_delta VALUES ('PUMP-007',2),('FAN-014',3);UPDATE device_update AS dSET service_count = d.service_count + x.add_count, version = d.version + 1FROM service_delta AS xWHERE d.code = x.code;SELECT code, service_count, version FROM device_update ORDER BY code;Make the join produce at most one source row per target row. If the join produces multiple source rows for one target, SQLite uses one arbitrarily; which row wins can change between runs or releases. Aggregate or enforce uniqueness before the update.
Optional UPDATE/DELETE ORDER BY and LIMIT syntax depends on SQLITE_ENABLE_UPDATE_DELETE_LIMIT. This chapter does not rely on that compile-time feature.
Optimistic concurrency: update only the version you read
An application often reads a row, lets a user edit it, then writes later. Another writer may have changed the row in between. A simple optimistic pattern includes the previously read version in the WHERE clause.
-- Application previously read version = 2.BEGIN;UPDATE device_updateSET status='maintenance', version=version+1WHERE code='PUMP-007' AND version=2;SELECT changes() AS matched_version;COMMIT;If changes() is 0, either the row disappeared or its version changed. Treat that as a concurrency conflict, re-read state, and decide whether to retry, merge, or reject. Do not silently overwrite newer data.
Lab: deliberately omit WHERE, then recover
This experiment is safe only because the table is disposable and the transaction remains uncommitted. It proves why the workflow exists.
SELECT device_id, code, status FROM device_update ORDER BY device_id;BEGIN;-- Deliberate mistake: no WHERE. Every row is targeted.UPDATE device_update SET status='maintenance';SELECT changes() AS accidental_scope; -- expect all rowsSELECT device_id, code, status FROM device_update ORDER BY device_id;ROLLBACK;SELECT device_id, code, status FROM device_update ORDER BY device_id;After ROLLBACK, the table returns to its pre-transaction state. If you had committed first, rollback would no longer be available; recovery would require a compensating operation or restore strategy.
UPDATE checkpoint
Choose the safe interpretation.
- Why SELECT with the same predicate before UPDATE?
- What should you do if changes() is 27 but you expected 1?
- Is UPDATE FROM portable standard SQL?
- What risk exists if one target joins multiple source rows?
- What does a version predicate detect?
Review the answers
Previewing makes the target set visible. An unexpected changes() count is a reason to rollback and investigate. UPDATE FROM is a SQLite-supported non-standard extension. Multiple matching source rows make the chosen source arbitrary. A version predicate detects that state changed since the caller read it.
Production judgment and bridge
For high-risk writes, make expected row counts part of the command contract, not an afterthought. Use transactions, bound values, stable predicates, and version checks when users or processes can edit the same entity. Next, the same scope discipline is applied to DELETE—where foreign-key cascades can make the physical impact larger than the directly targeted row set.