SQL UPSERT: INSERT ON CONFLICT, MERGE Statements, and Duplication Handling
The dialect split between ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE, why DO NOTHING breaks RETURNING, and the deadlocks bulk upserts cause.

Quick Summary
An upsert is "insert this row, or update it if the key already exists", executed as one statement so two concurrent writers cannot both insert. Every major engine has a different spelling: INSERT ... ON CONFLICT in PostgreSQL and SQLite, INSERT ... ON DUPLICATE KEY UPDATE in MySQL, MERGE in SQL Server, Oracle, BigQuery, Snowflake, and — since version 15 — PostgreSQL.
The spelling matters less than the contract. The conflict has to be declared against a real unique constraint, the statement has to touch each target row at most once, and the "did it insert or update?" answer differs per dialect. SELECT then INSERT is not an upsert, no matter how short the gap looks; it fails the day two requests arrive together.
Basic syntax and query signatures
-- PostgreSQL / SQLite
INSERT INTO users (email, name, updated_at)
VALUES ('a@example.com', 'A', now())
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
updated_at = EXCLUDED.updated_at
RETURNING id, (xmax = 0) AS inserted;-- MySQL 8.0.19+ row alias (VALUES() is deprecated)
INSERT INTO users (email, name, updated_at)
VALUES ('a@example.com', 'A', NOW())
AS new
ON DUPLICATE KEY UPDATE
name = new.name,
updated_at = new.updated_at;-- Standard MERGE (PostgreSQL 15+, SQL Server, Oracle, BigQuery, Snowflake)
MERGE INTO users AS t
USING (VALUES ('a@example.com', 'A')) AS s(email, name)
ON t.email = s.email
WHEN MATCHED THEN
UPDATE SET name = s.name
WHEN NOT MATCHED THEN
INSERT (email, name) VALUES (s.email, s.name);ON CONFLICT (email) names the conflict target — the unique index or constraint whose violation triggers the fallback. Without a target, ON CONFLICT DO NOTHING catches every constraint; DO UPDATE requires a target, because the engine has to know which rule it is resolving.
Key comparison table
| Dialect | Syntax | Conflict target required | RETURNING | Row alias |
|---|---|---|---|---|
| PostgreSQL 9.5+ | ON CONFLICT (...) DO UPDATE/NOTHING | Yes for DO UPDATE | Yes | EXCLUDED |
| SQLite 3.24+ | ON CONFLICT (...) DO UPDATE/NOTHING | Yes for DO UPDATE | Yes (3.35+) | excluded |
| MySQL 5.7+ | ON DUPLICATE KEY UPDATE | No — any unique key | No | AS new (8.0.19+) |
| SQL Server | MERGE | Join condition | OUTPUT clause | $action |
| Oracle | MERGE | Join condition | RETURNING INTO | — |
| PostgreSQL 15+ | MERGE | Join condition | No | — |
| BigQuery | MERGE | Join condition | No | — |
| Behaviour | DO NOTHING | DO UPDATE |
|---|---|---|
| Locks the conflicting row | No | Yes, until commit |
| Returns the skipped row | No | Yes |
| Requires a conflict target | No | Yes |
Fires INSERT triggers on conflict | No | No; UPDATE triggers fire |
| Can deadlock in concurrent batches | Rarely | Yes, if input order differs |
Practical edge cases and deep dives
1. ON CONFLICT with no matching unique index
-- ERROR 42P10: there is no unique or exclusion constraint matching the ON CONFLICT specification
INSERT INTO users (email, name) VALUES ('a@example.com', 'A')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;The target must match a real unique index, and it has to match it exactly — an index on lower(email) will not satisfy ON CONFLICT (email), and a partial unique index (WHERE deleted_at IS NULL) needs the same predicate in the target:
CREATE UNIQUE INDEX users_email_active_uq ON users (lower(email)) WHERE deleted_at IS NULL;
INSERT INTO users (email, name) VALUES ('a@example.com', 'A')
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL DO UPDATE SET name = EXCLUDED.name;This is where soft-delete designs meet upserts: UNIQUE(email) alone blocks re-registering after a soft delete, and dropping the constraint removes the very thing ON CONFLICT needs. The partial unique index is the shape that keeps both.
2. DO NOTHING returns nothing, and that breaks the RETURNING idiom
-- Expects the id, gets zero rows when the row already existed
INSERT INTO users (email) VALUES ('a@example.com')
ON CONFLICT (email) DO NOTHING
RETURNING id;RETURNING reports the rows the statement actually wrote, so skipped rows are absent — the caller receives nothing and cannot tell "already exists" from "failed". Three options: use DO UPDATE SET email = EXCLUDED.email (a no-op write that still returns the row and takes a lock), run a follow-up SELECT, or use the PostgreSQL-only xmax = 0 trick shown above, which is true for freshly inserted rows and false for updated ones. That trick depends on an internal system column and should be documented where it is used.
3. "Cannot affect row a second time"
-- ERROR 21000: ON CONFLICT DO UPDATE command cannot affect row a second time
INSERT INTO users (email, name)
VALUES ('a@example.com', 'A'), ('a@example.com', 'B')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;PostgreSQL refuses a statement that would update the same target row twice, because the result would depend on input order. Deduplicate the batch before inserting:
INSERT INTO users (email, name)
SELECT DISTINCT ON (email) email, name
FROM (VALUES ('a@example.com','A'), ('a@example.com','B')) AS v(email, name)
ORDER BY email, name DESC
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;MySQL takes the other route and applies the assignments in statement order, silently keeping the last one — so the same batch gives an answer, just not necessarily the one you intended. For bulk loads, dedupe in the application or in a staging table, and you get deterministic behaviour on every engine.
4. Bulk upserts and the deadlock they cause
DO UPDATE locks the conflicting row until the transaction commits. Two concurrent batches that contain the same keys in different orders will take those locks in different orders and deadlock:
ERROR: deadlock detected (40P01)
DETAIL: Process 3312 waits for ShareLock on transaction 9001; blocked by process 3311.Sort the input by the conflict key before inserting, so every transaction acquires locks in the same order. Failing that, split into chunks of a few hundred rows to shrink the lock window, and retry on 40P01. Neither fix is optional at scale: an unordered bulk upsert from a job that runs in parallel is a deadlock generator.
5. MERGE is not a drop-in replacement
MERGE INTO inventory AS t
USING staging AS s ON t.sku = s.sku
WHEN MATCHED AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);Two differences bite. First, the source must produce at most one row per target row: SQL Server raises "The MERGE statement attempted to UPDATE or DELETE the same row more than once" and PostgreSQL 15+ raises the cardinality violation above. Second, concurrent inserts are not protected the way ON CONFLICT protects them — MERGE reads the target, then writes, and a row inserted between those steps by another session can cause a duplicate key error or a lost update. PostgreSQL's MERGE is documented as not taking a lock on the target rows it did not match, so it is not atomic against concurrent inserts of the same key.
The practical guidance: use ON CONFLICT/ON DUPLICATE KEY when the conflict is a unique key and you are inserting; use MERGE when the operation is genuinely a set reconciliation across multiple conditions, run under a transaction that can afford the locks.
Common mistakes and runtime errors
| Error or symptom | Cause | Fix |
|---|---|---|
there is no unique or exclusion constraint matching the ON CONFLICT specification (42P10) | Target does not match a unique index | Create the index, or name the exact expression and predicate |
ON CONFLICT DO UPDATE command cannot affect row a second time (21000) | Duplicate keys inside one statement | Deduplicate with DISTINCT ON or a staging table |
RETURNING returns no rows for existing keys | DO NOTHING skips them | DO UPDATE, a follow-up SELECT, or xmax = 0 |
Column 'name' in field list is ambiguous (MySQL) | Unqualified column in ON DUPLICATE KEY UPDATE | Alias the new row (AS new) and qualify |
VALUES() deprecation warning (MySQL 8.0.20+) | Old row-reference function | Use the row alias |
| Affected rows = 2 for a single upsert | MySQL counts an update as delete + insert | Compare with 1/0 in the client, not == 1 |
| Deadlock on a parallel bulk upsert | Lock acquisition in inconsistent order | Sort by key, chunk, retry |
MERGE updates the same row twice | Source has duplicate keys | Deduplicate the source |
Duplicate key error during MERGE under concurrency | MERGE does not lock unmatched rows | Use ON CONFLICT, or serialise the reconciler |
ON CONFLICT fires no trigger | Conflict path is an update, not an insert | Attach the logic to the UPDATE trigger |
One last trap sits outside the statement: an upsert does not validate what it writes. ON CONFLICT DO UPDATE SET name = EXCLUDED.name will happily overwrite a curated value with a stale one from an import, because it cannot tell which side is fresher. Add a guard — WHERE users.updated_at < EXCLUDED.updated_at — and the same statement becomes an idempotent synchronisation instead of a race to the bottom.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.