UPSERT in SQLite

"Upsert" means insert a row, or update it if a row with the same key already exists. SQLite gives you three tools that look similar but behave very differently: the true ON CONFLICT DO UPDATE clause (added in version 3.24.0, 2018-06-04), and the older INSERT OR REPLACE and INSERT OR IGNORE shortcuts. Picking the wrong one quietly deletes data, so the differences matter.

This guide covers all three, the excluded pseudo-table, and how each interacts with triggers, foreign keys, and auto-increment IDs.

ON CONFLICT DO UPDATE: the real UPSERT

This is what you usually want. It follows PostgreSQL's syntax.

INSERT INTO inventory (sku, qty)
VALUES ('A1', 10)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

If no row with sku = 'A1' exists, this inserts one with qty = 10. If one already exists, it runs the UPDATE instead, adding the incoming quantity to the existing one.

The excluded table is the key concept. Inside the DO UPDATE, an unqualified column name (qty) refers to the existing row's current value. The excluded. qualifier refers to the value that the failed INSERT tried to insert. So qty = qty + excluded.qty reads as "existing quantity plus the new quantity." qty = excluded.qty would just overwrite with the new value.

The conflict target is required (almost always)

The ON CONFLICT(sku) part is the conflict target. It names the column or constraint whose uniqueness violation triggers the upsert. It must match an actual UNIQUE, PRIMARY KEY, or unique index. You can omit the target only on the final ON CONFLICT clause, in which case it fires for any uniqueness violation not caught earlier:

INSERT INTO inventory (sku, qty) VALUES ('A1', 10)
ON CONFLICT DO NOTHING;

DO NOTHING makes the conflicting insert a silent no-op, which is the explicit, modern equivalent of INSERT OR IGNORE.

Filtering the update with WHERE

You can add a WHERE clause so the update only applies under a condition:

INSERT INTO inventory (sku, qty) VALUES ('A1', 10)
ON CONFLICT(sku) DO UPDATE SET qty = excluded.qty
WHERE excluded.qty > inventory.qty;

Here the row only updates if the incoming quantity is larger than what is stored.

One important limit: UPSERT fires only for uniqueness constraints (UNIQUE, PRIMARY KEY, unique index). It does not intervene for failed NOT NULL, CHECK, or foreign-key constraints. Those still raise errors as usual.

INSERT OR REPLACE: convenient but destructive

INSERT OR REPLACE INTO inventory (sku, qty) VALUES ('A1', 10);
-- REPLACE INTO ... is the same thing

This looks like an upsert and is shorter to type, but it does not update the row. On a uniqueness conflict it deletes the existing row and inserts a brand-new one. The consequences trip people up constantly:

• Columns you did not list are reset to their defaults, not preserved. If the table has a last_updated or notes column you left out of the INSERT, it is wiped.
• DELETE triggers and INSERT triggers fire, because a delete and an insert actually happen.
• Foreign keys with ON DELETE CASCADE will cascade, potentially deleting child rows you meant to keep.
• An INTEGER PRIMARY KEY / rowid can change if the conflict was on a different unique column, breaking any external references to the old ID.

Use INSERT OR REPLACE only when you genuinely want to throw away the old row and you have listed every column. For incrementing a counter or patching a few fields, use ON CONFLICT DO UPDATE instead.

INSERT OR IGNORE: skip on conflict

INSERT OR IGNORE INTO inventory (sku, qty) VALUES ('A1', 10);

If the row already exists, this does nothing and moves on without an error. It is useful for "insert if not present" seeding. Be aware that OR IGNORE also silently skips rows that violate NOT NULL or CHECK constraints, not just uniqueness, so a bug that produces invalid rows can be masked. ON CONFLICT DO NOTHING is more precise because it only ignores uniqueness conflicts.

Which one to use

• Increment, merge, or patch fields on conflict -> ON CONFLICT(col) DO UPDATE. This is the default choice.
• Insert only if absent, ignore otherwise -> ON CONFLICT DO NOTHING (or INSERT OR IGNORE if you accept its broader silence).
• Fully replace the row and you listed every column -> INSERT OR REPLACE. Otherwise avoid it.

Common mistakes

• Reaching for INSERT OR REPLACE as an upsert. It deletes the row, resetting unlisted columns to defaults and firing cascades. Use ON CONFLICT DO UPDATE to actually update.
• Omitting the conflict target on a non-final clause. It is required except on the last ON CONFLICT. SQLite will reject it.
• Confusing excluded.col with the existing value. Unqualified col is the current row; excluded.col is the incoming value. Mixing them up gives wrong arithmetic.
• Expecting UPSERT to catch NOT NULL or foreign-key failures. It only handles uniqueness constraints; everything else still errors.
• Pointing the conflict target at a non-unique column. The target must be backed by a UNIQUE, PRIMARY KEY, or unique index, or the statement fails.

For wrapping a batch of upserts in a single fast transaction, see the SQLite transactions guide. For making sure your conflict target is actually indexed, see the indexes guide.

When you are working out the right excluded-versus-existing expression for a merge, Mako's AI autocomplete can draft the ON CONFLICT DO UPDATE clause against your live schema so you can see which columns each side refers to before you run it.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.