Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Data Manipulation#DML
3D illustration of incoming data rows updating existing duplicate keys or inserting fresh records without collision errors

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

sql
-- 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;
sql
-- 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;
sql
-- 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

DialectSyntaxConflict target requiredRETURNINGRow alias
PostgreSQL 9.5+ON CONFLICT (...) DO UPDATE/NOTHINGYes for DO UPDATEYesEXCLUDED
SQLite 3.24+ON CONFLICT (...) DO UPDATE/NOTHINGYes for DO UPDATEYes (3.35+)excluded
MySQL 5.7+ON DUPLICATE KEY UPDATENo — any unique keyNoAS new (8.0.19+)
SQL ServerMERGEJoin conditionOUTPUT clause$action
OracleMERGEJoin conditionRETURNING INTO—
PostgreSQL 15+MERGEJoin conditionNo—
BigQueryMERGEJoin conditionNo—
BehaviourDO NOTHINGDO UPDATE
Locks the conflicting rowNoYes, until commit
Returns the skipped rowNoYes
Requires a conflict targetNoYes
Fires INSERT triggers on conflictNoNo; UPDATE triggers fire
Can deadlock in concurrent batchesRarelyYes, if input order differs

Practical edge cases and deep dives

1. ON CONFLICT with no matching unique index

sql
-- 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:

sql
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

sql
-- 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"

sql
-- 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:

sql
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:

code
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

sql
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 symptomCauseFix
there is no unique or exclusion constraint matching the ON CONFLICT specification (42P10)Target does not match a unique indexCreate 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 statementDeduplicate with DISTINCT ON or a staging table
RETURNING returns no rows for existing keysDO NOTHING skips themDO UPDATE, a follow-up SELECT, or xmax = 0
Column 'name' in field list is ambiguous (MySQL)Unqualified column in ON DUPLICATE KEY UPDATEAlias the new row (AS new) and qualify
VALUES() deprecation warning (MySQL 8.0.20+)Old row-reference functionUse the row alias
Affected rows = 2 for a single upsertMySQL counts an update as delete + insertCompare with 1/0 in the client, not == 1
Deadlock on a parallel bulk upsertLock acquisition in inconsistent orderSort by key, chunk, retry
MERGE updates the same row twiceSource has duplicate keysDeduplicate the source
Duplicate key error during MERGE under concurrencyMERGE does not lock unmatched rowsUse ON CONFLICT, or serialise the reconciler
ON CONFLICT fires no triggerConflict path is an update, not an insertAttach 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.