Chapter 06 · SQLite Data Modification: INSERT, UPDATE, DELETE, UPSERT, and RETURNING
UPSERT with ON CONFLICT DO NOTHING / DO UPDATE
Synchronize logical entities safely with uniqueness-driven UPSERT, excluded values, conditional updates, and idempotent ingestion—without confusing UPSERT with REPLACE.
Learning outcomes
UPSERT solves a common ingestion problem: a natural or alternate key says the logical entity already exists, so the attempted INSERT should take a controlled alternative path. SQLite's UPSERT is attached to INSERT and is driven only by uniqueness conflicts.
Define an UPSERT conflict target and connect it to a PRIMARY KEY, UNIQUE constraint, or unique index.
Use DO NOTHING and DO UPDATE intentionally.
Use the excluded pseudo-table to reference values from the attempted insert.
Explain why UPSERT is different from INSERT OR REPLACE.
Build conditional/idempotent update logic.
Reason about multi-row UPSERT where each input row takes its own conflict path.
The application problem: insert-or-reconcile by identity
Suppose FieldNotes receives device records from an upstream system. The natural synchronization key is external_key. A new key should create a row. An existing key should update selected attributes, not create a duplicate.
DROP TABLE IF EXISTS sync_device;CREATE TABLE sync_device( device_id INTEGER PRIMARY KEY, external_key TEXT NOT NULL UNIQUE, label TEXT NOT NULL, source_version INTEGER NOT NULL, updated_at TEXT NOT NULL);INSERT INTO sync_device(external_key,label,source_version,updated_at)VALUES ('asset-100','North Pump',1,'2026-08-12T06:00:00Z');The UNIQUE rule is not merely an error source; it is the conflict target that lets UPSERT identify the existing logical entity.
DO NOTHING: duplicate input becomes a no-op
INSERT INTO sync_device(external_key,label,source_version,updated_at)VALUES ('asset-100','Duplicate Feed',1,'2026-08-12T06:05:00Z')ON CONFLICT(external_key) DO NOTHING;SELECT external_key,label,source_version FROM sync_device;The original row remains unchanged. This is much narrower and more explainable than generic OR IGNORE: UPSERT responds to the specified uniqueness conflict, while unrelated NOT NULL, CHECK, or foreign-key failures are still errors.
DO UPDATE and excluded values
Inside DO UPDATE, unqualified target columns refer to the existing row. excluded.column refers to the value that the attempted INSERT would have used.
INSERT INTO sync_device(external_key,label,source_version,updated_at)VALUES ('asset-100','North Pump Mk II',2,'2026-08-12T06:10:00Z')ON CONFLICT(external_key) DO UPDATE SET label = excluded.label, source_version = excluded.source_version, updated_at = excluded.updated_at;SELECT external_key,label,source_version,updated_at FROM sync_device;The row keeps its existing device_id. That identity preservation is one reason UPSERT is usually a better synchronization primitive than REPLACE.
Conditional UPSERT prevents stale data from overwriting newer data
A WHERE clause on DO UPDATE can turn the update path into a no-op. This is useful for idempotent feeds carrying monotonic versions or timestamps.
INSERT INTO sync_device(external_key,label,source_version,updated_at)VALUES ('asset-100','Stale Name',1,'2026-08-12T05:00:00Z')ON CONFLICT(external_key) DO UPDATE SET label = excluded.label, source_version = excluded.source_version, updated_at = excluded.updated_atWHERE excluded.source_version > sync_device.source_version;SELECT external_key,label,source_version FROM sync_device;-- Existing version 2 remains unchanged.Repeated delivery of the same or older message no longer regresses the row. This is a database-level aid, but it still depends on a trustworthy source-version contract.
UPSERT is not INSERT OR REPLACE
Chapter 5 showed that REPLACE resolves uniqueness by deleting conflicting row(s) and continuing the INSERT. That can change row identity and activate delete/cascade behavior. UPSERT DO UPDATE updates the existing row in place.
| Mechanism | Conflict behavior | Lifecycle consequence |
|---|---|---|
INSERT ... ON CONFLICT ... DO UPDATE | Updates selected columns of the existing row. | Existing row identity can remain stable; explicit update semantics. |
INSERT OR REPLACE | Deletes conflicting row(s), then inserts. | Can replace row identity and interact with delete triggers/FKs; not ordinary UPDATE. |
ON CONFLICT ... DO NOTHING | Known uniqueness conflict becomes a no-op. | No replacement or update occurs. |
If child rows reference an INTEGER PRIMARY KEY, delete-and-insert semantics can be materially different from updating the existing logical entity.
Multi-row UPSERT: each input row chooses its own path
INSERT INTO sync_device(external_key,label,source_version,updated_at) VALUES('asset-100','North Pump v3',3,'2026-08-12T06:20:00Z'),('asset-200','South Fan',1,'2026-08-12T06:20:00Z'),('asset-300','Lab Sensor',1,'2026-08-12T06:20:00Z')ON CONFLICT(external_key) DO UPDATE SET label=excluded.label, source_version=excluded.source_version, updated_at=excluded.updated_atWHERE excluded.source_version > sync_device.source_version;SELECT external_key,label,source_versionFROM sync_device ORDER BY external_key;SQLite evaluates UPSERT independently for each input row. Here asset-100 takes the update path while the two new keys take the insert path.
INSERT ... SELECT parsing detail
When UPSERT follows an INSERT whose source is SELECT, SQLite recommends giving the SELECT a WHERE clause—even WHERE true—to avoid parser ambiguity around the ON keyword.
INSERT INTO sync_device(external_key,label,source_version,updated_at)SELECT external_key,label,source_version,updated_atFROM incoming_deviceWHERE trueON CONFLICT(external_key) DO UPDATE SET label=excluded.label, source_version=excluded.source_version, updated_at=excluded.updated_at;Also remember that UPSERT only responds to uniqueness constraints. A NOT NULL, CHECK, or foreign-key violation still fails unless separately handled by its own defined policy.
Sync lab and verification
Run the same feed twice. The second run should be idempotent: it may evaluate UPSERT again, but the durable logical state should not multiply or regress.
DELETE FROM sync_device;INSERT INTO sync_device(external_key,label,source_version,updated_at) VALUES('asset-100','Pump',1,'2026-08-12T06:00:00Z'),('asset-200','Fan',1,'2026-08-12T06:00:00Z')ON CONFLICT(external_key) DO UPDATE SET label=excluded.label, source_version=excluded.source_version, updated_at=excluded.updated_atWHERE excluded.source_version > sync_device.source_version;-- Repeat exactly the same statement.INSERT INTO sync_device(external_key,label,source_version,updated_at) VALUES('asset-100','Pump',1,'2026-08-12T06:00:00Z'),('asset-200','Fan',1,'2026-08-12T06:00:00Z')ON CONFLICT(external_key) DO UPDATE SET label=excluded.label, source_version=excluded.source_version, updated_at=excluded.updated_atWHERE excluded.source_version > sync_device.source_version;SELECT count(*) AS rows_after_replay FROM sync_device; -- 2The count remains two. The uniqueness key defines entity identity and the conditional update prevents equal-version replays from changing state.
UPSERT checkpoint
Identify the write path.
- What kinds of constraints trigger SQLite UPSERT?
- What does excluded.label mean?
- Why can DO UPDATE preserve identity better than REPLACE?
- Can two rows in one VALUES list take different UPSERT paths?
- Why add WHERE true to an INSERT SELECT source when an UPSERT follows?
Review the answers
UPSERT is driven by PRIMARY KEY/UNIQUE/unique-index conflicts. excluded.label is the value proposed by the attempted INSERT. DO UPDATE changes the existing row instead of delete-and-insert replacement. Multi-row decisions are made separately per input row. WHERE true removes parser ambiguity between join ON syntax and the UPSERT ON CONFLICT clause.
Summary and bridge
UPSERT makes uniqueness part of an ingestion contract: new identities insert, known identities update or do nothing. The final lesson makes these writes observable to callers using RETURNING and change counters, while carefully separating “rows directly changed” from cascades, triggers, business success, and driver-specific rowcount behavior.