Insert, Or Update If It's Already There
A common need: insert a row, but if a row with the same key already exists, update it instead. Without UPSERT, you'd write a SELECT first, then branch into INSERT or UPDATE — two round trips and a race condition between them.
SQLite's UPSERT does it in one statement:
First time you run it, the row is inserted. Run it again with a different price and the same sku, and the existing row is updated in place. No duplicate, no error.
The Anatomy of ON CONFLICT
The full shape:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
Three pieces matter:
conflict_target— the column or columns with aUNIQUEorPRIMARY KEYconstraint you expect to collide on. SQLite uses this to pick which index to watch.DO UPDATE SET ...— what to change on the existing row when a collision happens. (OrDO NOTHINGto skip silently.)- Optional
WHERE— extra condition that has to be true for the update to actually run.
The conflict target has to match a real unique constraint. ON CONFLICT(price) won't compile if price isn't unique — SQLite has nothing to detect a conflict against.
DO NOTHING: Insert If Absent, Otherwise Skip
The simpler variant. Useful when you're seeding data or recording events and duplicates should just be quietly ignored:
The second insert hits the same event_id and would normally raise UNIQUE constraint failed. With DO NOTHING, SQLite just skips it. No exception, no row affected.
This is the "idempotent insert" people often reach for INSERT OR IGNORE for. UPSERT's DO NOTHING does the same job and composes better with WHERE and RETURNING clauses.
The excluded Pseudo-Table
When a conflict fires, you suddenly have two rows in play: the existing one in the table, and the new one you tried to insert. SQLite gives you a way to talk about both.
- Bare column names (
price,name) refer to the existing row. excluded.columnrefers to the incoming row that got rejected.
quantity = quantity + excluded.quantity reads as "the existing quantity plus the new one." After two inserts, A-100 has quantity 8. This pattern — accumulating into an existing row — is one of UPSERT's most useful tricks.
A Conditional UPSERT With WHERE
The trailing WHERE lets you skip the update unless some condition holds. It runs against the existing row (and can reference excluded.* for the incoming one):
The new row carries an older updated_at, so the WHERE is false and the update is skipped. The existing row keeps its newer price. Swap the dates and the update runs. This is the standard "only overwrite with fresher data" pattern.
Upserting Multiple Rows
VALUES can hold many rows, and ON CONFLICT applies to each one independently:
A-100 collides and gets updated. A-200 and A-300 are new and get inserted. One statement, mixed insert-and-update result. This is a clean way to sync a batch of records from an external source.
UPSERT vs INSERT OR REPLACE
INSERT OR REPLACE looks like it does the same thing. It doesn't.
notes is gone. INSERT OR REPLACE deleted row 1 entirely and inserted a fresh one — any column you didn't list got reset to NULL or its default. It also fires DELETE triggers and cascades through ON DELETE foreign keys.
UPSERT preserves the row:
notes is still there. Only the columns named in SET changed. Reach for UPSERT by default; reach for INSERT OR REPLACE only when you genuinely want delete-and-reinsert semantics.
Multiple Conflict Targets
If a row could collide on more than one constraint, you can chain ON CONFLICT clauses:
Whichever constraint fires first wins, and that branch's DO UPDATE runs. In practice, most tables have one obvious conflict target — the primary key or a single unique column — and you'll rarely need more than one clause.
Common Pitfalls
A few things that bite people:
- No matching unique index, no UPSERT.
ON CONFLICT(col)requirescolto be aPRIMARY KEYor have aUNIQUEconstraint. Otherwise SQLite errors out with "no such constraint." DO UPDATEdoesn't fire if there's no conflict. It's an alternative to the insert, not extra behavior. The first time a key is seen, only the insert runs.excludedis read-only. You can read from it but not write to it. The target ofSETis always the existing row.- Generated
INTEGER PRIMARY KEYrowids. If you don't supply the id, every insert gets a new one — there's nothing to conflict with. UPSERT only makes sense when the conflicting column has a deterministic value supplied by the caller.
Next: RETURNING
UPSERT tells you nothing about which rows were inserted versus updated, or what their final values look like. For that, you want the RETURNING clause — it hands back the affected rows in the same statement, no follow-up SELECT needed. That's next.
Frequently Asked Questions
What is UPSERT in SQLite?
UPSERT is an INSERT that turns into an UPDATE (or a no-op) when it would otherwise violate a UNIQUE or PRIMARY KEY constraint. You write it as INSERT ... ON CONFLICT(column) DO UPDATE SET ... or DO NOTHING. SQLite has supported it since version 3.24.0 (2018).
What is the excluded table in SQLite UPSERT?
excluded is a special pseudo-table that holds the row you tried to insert. Inside DO UPDATE SET ..., you reference the existing row by column name and the rejected row as excluded.column. So SET price = excluded.price means 'overwrite price with whatever the new INSERT was bringing in'.
What's the difference between INSERT OR REPLACE and UPSERT?
INSERT OR REPLACE deletes the conflicting row and inserts a fresh one — that fires DELETE triggers, breaks foreign keys with ON DELETE CASCADE, and resets every column to defaults. UPSERT updates the existing row in place, so only the columns you name in SET change. Prefer UPSERT unless you actually want a delete-and-reinsert.
Can I upsert multiple rows at once in SQLite?
Yes. INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... works fine. Each row is checked against the conflict target individually, and the excluded row inside DO UPDATE refers to whichever incoming row triggered the conflict.
