Imagine re-running the same import every night to keep a table in sync. A plain INSERT blows up the moment it hits a row that already exists — the primary key collides, the statement fails, and your import halts halfway through. You could write code that first checks each row and then decides INSERT or UPDATE, but SQL folds that dance into a single statement called an upsert: insert if the key is new, update if it is not.
The name is a portmanteau of update or insert, and it is the right tool whenever data may arrive more than once — nightly syncs, form re-submissions, retrying a failed batch. One statement handles both outcomes, so you never have to choose the branch yourself.
flowchart TD
I["INSERT attempted"] --> C{"conflict on id?"}
C -->|"no"| INS["row inserted"]
C -->|"yes"| U["ON CONFLICT<br/>DO UPDATE"]
U --> UPD["existing row updated"]
style INS fill:#0e7490,color:#fff
style UPD fill:#0e7490,color:#fff
The shape of an upsert
An upsert is an INSERT with an ON CONFLICT clause bolted on. The clause tells SQLite what to do when the insert would break a uniqueness rule:
INSERT INTO employees (id, salary)
VALUES (1, 750000)
ON CONFLICT(id) DO UPDATE SET salary = excluded.salary;
The ON CONFLICT(id) names the column whose uniqueness would be violated — here the primary key id. Because employee 1 already exists, the insert conflicts, and DO UPDATE SET salary = excluded.salary reroutes into an update instead of erroring. Run it against a new id and the same statement inserts, because there is no conflict to trigger.
-- The INSERT half must be a complete, legal row even when it always conflicts: -- employees.name is NOT NULL, so omitting it fails before DO UPDATE is reached. INSERT INTO employees (id, name, salary) VALUES (1, 'Ada Nilsen', 750000) ON CONFLICT(id) DO UPDATE SET salary = excluded.salary; SELECT name, salary FROM employees WHERE id = 1;
The excluded keyword
The clever part is excluded.salary. When the insert conflicts, SQLite has two versions of the row in mind: the one already in the table, and the new one you just tried to insert. The new one is called excluded — it was excluded from actually being inserted because it lost the conflict.
SET salary = excluded.salary therefore means "set the existing row's salary to the value the incoming row was carrying." Write SET salary = salary by mistake and you assign the column to its own current value — the update runs but changes nothing, which is a bug that hides in plain sight. The word excluded is what reaches across to the new data.
flowchart LR ROW["row already in the table"] --> SET["SET salary = salary"] NEW["the row you tried to insert"] --> EXC["excluded.salary"] EXC --> SET style EXC fill:#b45309,color:#fff
DO NOTHING: ignore the conflict
Sometimes you do not want to update at all — you just want a re-import to skip rows that already exist without complaining. ON CONFLICT(id) DO NOTHING does exactly that: on a conflict, the statement quietly succeeds and changes nothing, leaving the existing row untouched and moving on.
This is the kindest choice when the existing data is authoritative and the incoming batch is only meant to fill gaps. DO UPDATE is for when the incoming data is newer and should overwrite. Picking between them is a question of which side wins when they disagree.
INSERT INTO departments (id, name, city) VALUES (1, 'Engineering', 'Oslo') ON CONFLICT(id) DO NOTHING; SELECT COUNT(*) AS total FROM departments;
flowchart LR
I["INSERT ... ON CONFLICT(id)"] --> C{"conflict?"}
C -->|"no"| A["row added"]
C -->|"yes, DO NOTHING"| N["nothing changes"]
C -->|"yes, DO UPDATE"| U["row updated"]
style A fill:#0e7490,color:#fff
style U fill:#0e7490,color:#fff
style N fill:#1e293b,color:#fff
One statement, two outcomes
The real power of an upsert is that the same statement handles both branches. Point it at an id that exists and it updates; point it at an id that is new and it inserts. You do not branch in your application code, and a batch of mixed new-and-existing rows flows through a single statement without anyone deciding which is which. That is why upserts are the backbone of every import, sync, and retry loop — the database works out the branch for you, one row at a time.
INSERT INTO employees (id, name, department_id, role, salary) VALUES (17, 'Nora Lind', 5, 'Researcher', 740000) ON CONFLICT(id) DO UPDATE SET salary = excluded.salary; SELECT id, name, role FROM employees WHERE id = 17;
Upsert employee id 1: set salary to 750000. Since id 1 already exists, the conflict should UPDATE the salary rather than fail.
INSERT INTO employees (id, salary) VALUES (1, 750000) -- add the ON CONFLICT ... DO UPDATE clause ;
Target the primary key with
ON CONFLICT(id).Use
DO UPDATE SET salary = excluded.salaryto bring in the new value.Every NOT NULL column needs a value in the INSERT half — the row is checked before ON CONFLICT decides to update instead.
INSERT INTO employees (id, name, salary) VALUES (1, 'Ada Nilsen', 750000) ON CONFLICT(id) DO UPDATE SET salary = excluded.salary;
Upsert a brand-new employee with id 20. Because id 20 does not exist yet, the same statement should INSERT the row. Provide name 'Tom Reed', department 2, role 'Marketer', salary 520000.
INSERT INTO employees (id, name, department_id, role, salary) VALUES (20, 'Tom Reed', 2, 'Marketer', 520000) -- add ON CONFLICT(id) DO UPDATE SET salary = excluded.salary ;
The full INSERT lists all five columns and values.
Append
ON CONFLICT(id) DO UPDATE SET salary = excluded.salary— it only fires on a conflict.
INSERT INTO employees (id, name, department_id, role, salary) VALUES (20, 'Tom Reed', 2, 'Marketer', 520000) ON CONFLICT(id) DO UPDATE SET salary = excluded.salary;
This upsert is meant to set employee id 3's salary to 950000, but it sets salary to its OWN current value — it reads salary instead of excluded.salary, so nothing changes. Fix it so the incoming value is used.
INSERT INTO employees (id, salary) VALUES (3, 950000) ON CONFLICT(id) DO UPDATE SET salary = salary;
salary = salarycopies the existing value onto itself.Use
excluded.salaryto reach the value you tried to insert.Every NOT NULL column needs a value in the INSERT half — the row is checked before ON CONFLICT decides to update instead.
INSERT INTO employees (id, name, salary) VALUES (3, 'Chen Wei', 950000) ON CONFLICT(id) DO UPDATE SET salary = excluded.salary;
Re-insert the Engineering department (1, 'Engineering', 'Oslo'). If it already exists, do nothing instead of erroring — confirm the row count does not grow.
INSERT INTO departments (id, name, city) VALUES (1, 'Engineering', 'Oslo') -- add ON CONFLICT ... DO NOTHING ;
Use
ON CONFLICT(id) DO NOTHING.The verify counts departments — it should stay at 5, proving nothing was added.
INSERT INTO departments (id, name, city) VALUES (1, 'Engineering', 'Oslo') ON CONFLICT(id) DO NOTHING;
You write ... ON CONFLICT(email) DO UPDATE SET ... but no column named email has a UNIQUE or PRIMARY KEY constraint. What happens?
ON CONFLICT can only target a column that has a uniqueness rule, otherwise the database has no way to tell when a conflict occurs. The primary key always qualifies; any other column must be declared UNIQUE first.
Recap
- An upsert inserts if the key is new and updates if it collides — one statement, both branches.
INSERT ... ON CONFLICT(id) DO UPDATE SET col = excluded.colmerges incoming data into existing rows.excludedis the row you tried to insert; the bare column name is the row already stored.ON CONFLICT(id) DO NOTHINGskips conflicts silently, leaving existing rows untouched.- ON CONFLICT needs a UNIQUE or PRIMARY KEY column to target.
Next you will group several of these modifications into a transaction, so a batch of changes either all land together or none of them do.