SQLite UPSERT: INSERT … ON CONFLICT DO UPDATE (Tested Examples)

Ninad Pathak

October 7, 2026

A plain INSERT stops with UNIQUE constraint failed when the key already exists. SQLite can update the existing row instead with INSERT … ON CONFLICT, since it has no UPSERT keyword. In this article I’ll walk you through how it works, and why INSERT OR REPLACE isn’t the same thing.

TLDR

An upsert inserts a row, or updates the existing row if the key is already taken.
In SQLite, write it as INSERT … ON CONFLICT(key) DO UPDATE, and use excluded. for the values you tried to insert.

Use ON CONFLICT(key) DO NOTHING to skip rows that already exist.
Don’t use INSERT OR REPLACE as an upsert. It deletes the old row and inserts a new one.
Upsert needs SQLite 3.24.0 or later.

What is an upsert in SQLite?

An upsert is an INSERT that turns into an UPDATE, or into nothing, when the row already exists. The name joins “update” and “insert”. SQLite’s docs define it as “a clause added to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint” (SQLite UPSERT).

A PRIMARY KEY or a UNIQUE column is a uniqueness constraint, and so is a unique index. Without an upsert, inserting a key that already exists fails. This uses the inventory table from Step 1 below, where A1 already exists:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 5);
Error: stepping, UNIQUE constraint failed: inventory.sku (19)

With an upsert, the same kind of insert updates the existing row instead. On a fresh copy of the table, where A1 has a quantity of 10:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 5) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;
sqlite> SELECT sku, qty FROM inventory;
sku  qty
---  ---
A1   15

UPSERT isn’t standard SQL. SQLite “follows the syntax established by PostgreSQL, with generalizations”, and it added upsert in version 3.24.0 (2018-06-04).

SQLite upsert syntax

An upsert is a normal INSERT followed by an ON CONFLICT clause:

INSERT INTO table_name (columns) VALUES (values)
ON CONFLICT (conflict_target) DO UPDATE SET column = excluded.column [WHERE condition];

INSERT INTO table_name (columns) VALUES (values)
ON CONFLICT (conflict_target) DO NOTHING;

Part	What it does
`conflict_target`	The unique column or columns to watch, such as `(sku)`
`DO UPDATE SET …`	The update to run instead of the insert
`excluded.column`	The value you tried to insert
`DO NOTHING`	Skip the row and carry on
`WHERE …`	Optional: run the update only when this is true

How to write an upsert

To show you how this works, I will use a small inventory table where sku is the primary key. Each step runs on the same database, one after another.

Step 1: Create a table with a unique key

sqlite> CREATE TABLE inventory (sku TEXT PRIMARY KEY, name TEXT NOT NULL, qty INTEGER NOT NULL DEFAULT 0, updated TEXT);
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 10);

Step 2: Insert or update with DO UPDATE

Inside DO UPDATE, a bare column name means the value already in the table. To use the value you tried to insert, the docs say to “add the special ‘excluded.’ table qualifier to the column name”. Here A1 exists, so its quantity grows. B2 doesn’t exist, so it’s inserted:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 5) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty, updated = '2026-10-07';
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook', 3) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty, updated = '2026-10-07';
sqlite> SELECT * FROM inventory ORDER BY sku;
sku  name      qty  updated
---  --------  ---  ----------
A1   Pen       15   2026-10-07
B2   Notebook  3

Step 3: Skip duplicates with DO NOTHING

DO NOTHING leaves the existing row alone, and the statement still succeeds:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen (dup)', 99) ON CONFLICT(sku) DO NOTHING;
sqlite> SELECT sku, name, qty FROM inventory WHERE sku = 'A1';
sku  name  qty
---  ----  ---
A1   Pen   15

Step 4: See what happened with RETURNING

Add RETURNING to get the final row back, whether it was inserted or updated:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 1) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty RETURNING sku, qty;
sku  qty
---  ---
A1   16

Examples

Each example below continues on the same inventory table.

Update only when a condition holds

A WHERE clause on DO UPDATE skips the update when it’s false. The incoming quantity is 0 here, so the name stays as it was:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook v2', 0) ON CONFLICT(sku) DO UPDATE SET name = excluded.name WHERE excluded.qty > 0;
sqlite> SELECT sku, name FROM inventory WHERE sku = 'B2';
sku  name
---  --------
B2   Notebook

Handle two unique columns

Since SQLite 3.35.0, one INSERT can carry several ON CONFLICT clauses. The docs explain that only “the first ON CONFLICT clause with a matching conflict target” runs for each row. I gave this table two unique columns, email and handle, to show the second clause firing:

sqlite> CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT UNIQUE, handle TEXT UNIQUE, note TEXT);
sqlite> INSERT INTO accounts VALUES (1, 'ada@example.com', 'ada', 'first');
sqlite> INSERT INTO accounts (email, handle, note) VALUES ('ada@example.com', 'ada2', 'by email') ON CONFLICT(email) DO UPDATE SET note = excluded.note ON CONFLICT(handle) DO UPDATE SET note = 'handle taken';
sqlite> INSERT INTO accounts (email, handle, note) VALUES ('new@example.com', 'ada', 'by handle') ON CONFLICT(email) DO UPDATE SET note = excluded.note ON CONFLICT(handle) DO UPDATE SET note = 'handle taken';
sqlite> SELECT * FROM accounts;
id  email            handle  note
--  ---------------  ------  ------------
1   ada@example.com  ada     handle taken

The first insert matched on email and set the note to “by email”. The second matched on handle, so the second clause ran and overwrote the note with “handle taken”.

Leave out the conflict target

Also since 3.35.0, “The conflict target may be omitted on the last ON CONFLICT clause in the INSERT statement”. It then catches any uniqueness conflict:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('A1', 'Pen', 1) ON CONFLICT DO UPDATE SET qty = qty + 1;
sqlite> SELECT sku, qty FROM inventory WHERE sku = 'A1';
sku  qty
---  ---
A1   17

Edge cases

These are the upsert behaviours that surprise people. Each one is tested below.

INSERT OR REPLACE is not an upsert

INSERT OR REPLACE looks similar, but on a conflict it “deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row” (ON CONFLICT clause). The row gets a new id, and the columns you didn’t supply go back to their defaults:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT, logins INTEGER DEFAULT 0);
sqlite> INSERT INTO users (email, name, logins) VALUES ('ada@example.com', 'Ada', 7);
sqlite> INSERT OR REPLACE INTO users (email, name) VALUES ('ada@example.com', 'Ada L.');
sqlite> SELECT * FROM users;
id  email            name    logins
--  ---------------  ------  ------
2   ada@example.com  Ada L.  0

The upsert updates the row in place, so the id and the logins count survive:

sqlite> DELETE FROM users; INSERT INTO users (id, email, name, logins) VALUES (1, 'ada@example.com', 'Ada', 7);
sqlite> INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada L.') ON CONFLICT(email) DO UPDATE SET name = excluded.name;
sqlite> SELECT * FROM users;
id  email            name    logins
--  ---------------  ------  ------
1   ada@example.com  Ada L.  7

INSERT … SELECT needs WHERE true

When the new rows come from a SELECT, the parser can’t tell whether ON starts the upsert or a join. The docs’ fix: “the SELECT statement should always include a WHERE clause, even if that WHERE clause is just ‘WHERE true’” (parsing ambiguity):

sqlite> CREATE TABLE staging (sku TEXT, name TEXT, qty INTEGER); INSERT INTO staging VALUES ('A1', 'Pen', 2), ('C3', 'Stapler', 4);
sqlite> INSERT INTO inventory (sku, name, qty) SELECT sku, name, qty FROM staging ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;
Error: in prepare, near "DO": syntax error
  LECT sku, name, qty FROM staging ON CONFLICT(sku) DO UPDATE SET qty = qty + ex
                                      error here ---^
sqlite> INSERT INTO inventory (sku, name, qty) SELECT sku, name, qty FROM staging WHERE true ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;
sqlite> SELECT sku, name, qty FROM inventory ORDER BY sku;
sku  name      qty
---  --------  ---
A1   Pen       19
B2   Notebook  3
C3   Stapler   4

changes() counts an update even when nothing changed

changes() reports 1 after a DO UPDATE, even when the new value equals the old one. DO NOTHING reports 0. If you need a count of rows that really changed, add WHERE qty IS NOT excluded.qty to the update. The last two statements below show it reporting 0 for the same value and 1 for a new one:

sqlite> SELECT qty FROM inventory WHERE sku = 'B2';
qty
---
3
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook', 3) ON CONFLICT(sku) DO UPDATE SET qty = excluded.qty; SELECT changes();
changes()
---------
1
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook', 3) ON CONFLICT(sku) DO NOTHING; SELECT changes();
changes()
---------
0
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook', 3) ON CONFLICT(sku) DO UPDATE SET qty = excluded.qty WHERE qty IS NOT excluded.qty; SELECT changes();
changes()
---------
0
sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('B2', 'Notebook', 4) ON CONFLICT(sku) DO UPDATE SET qty = excluded.qty WHERE qty IS NOT excluded.qty; SELECT changes();
changes()
---------
1

Upsert only catches uniqueness conflicts

“UPSERT does not intervene for failed NOT NULL, CHECK, or foreign key constraints”. A missing name still fails:

sqlite> INSERT INTO inventory (sku, name, qty) VALUES ('Z9', NULL, 1) ON CONFLICT(sku) DO NOTHING;
Error: stepping, NOT NULL constraint failed: inventory.name (19)

Conclusion

For insert-or-update in SQLite, write INSERT … ON CONFLICT(key) DO UPDATE, and use excluded. for the incoming values. Use DO NOTHING to skip duplicates. Avoid INSERT OR REPLACE unless you really want the old row deleted, because it resets the id and every column you didn’t supply.