SQL 98 🛢️ Upsert Patterns: MERGE, ON CONFLICT, and ON DUPLICATE KEY - KandZ

⏱️ Estimated read: 15 min | |

An upsert is a single operation that inserts a row if it does not exist, or updates it if it does. The name is a contraction of “update” and “insert,” and the pattern exists because the alternative—a SELECT followed by an INSERT or UPDATE—has a race condition. Two concurrent transactions can both SELECT, both see that the row does not exist, and both attempt to INSERT. One succeeds, the other fails with a duplicate key violation, and the application must handle the failure and retry . The native upsert syntax eliminates that race by making the check and the write a single atomic operation.

Each database engine implements the pattern differently. PostgreSQL and SQLite use INSERT ... ON CONFLICT, which is the most precise syntax: it names the conflict target (the unique constraint that triggers the update) and uses the EXCLUDED pseudo-table to reference the values that would have been inserted . MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, which does not let you name the conflict target—any unique index violation triggers the update—and uses the deprecated VALUES() function (or a row alias) to reference the new values . SQL Server uses MERGE, which is the SQL standard syntax and the most verbose: it supports insert, update, and delete in one statement, which makes it suitable for full table synchronization rather than single-row upserts .

Key point: PostgreSQL’s ON CONFLICT is the safest and most precise upsert for concurrent workloads because it locks only the conflicting row; SQL Server’s MERGE is the most general but has race conditions under concurrency unless locking hints are added; MySQL’s ON DUPLICATE KEY UPDATE is the most dangerous on tables with multiple unique indexes because it does not let you specify which conflict triggers the update.

Why the three syntaxes behave differently

The conflict target problem. PostgreSQL’s ON CONFLICT (column) requires you to name the unique constraint or column that triggers the update. If the table has two unique constraints and a row conflicts on the one you did not name, the INSERT fails with a duplicate key error instead of updating . This is a deliberate design: the developer must state which conflict the upsert is designed to handle. MySQL’s ON DUPLICATE KEY UPDATE has no conflict target. If a row conflicts on any unique index, the update fires. If the table has multiple unique indexes and the update itself creates a conflict on a different index, the statement fails . The MySQL documentation recommends avoiding ON DUPLICATE KEY UPDATE on tables with multiple unique indexes for exactly this reason.

The concurrency problem. PostgreSQL’s ON CONFLICT is safe under concurrent writes. The engine locks the conflicting row for the duration of the statement and guarantees that exactly one insert or update fires per row . SQL Server’s MERGE is not safe by default. The match phase runs without exclusive locks, so two concurrent sessions can both see that a row does not exist, both attempt to insert, and one fails with a primary key violation . The fix is to add WITH (SERIALIZABLE) or WITH (UPDLOCK, HOLDLOCK) to the target table, which takes range locks that prevent the race. MySQL’s ON DUPLICATE KEY UPDATE is atomic for the conflicting row, but the multi-unique-index problem remains.

The insert-first problem. PostgreSQL’s ON CONFLICT tries the INSERT first and only falls back to UPDATE when the unique constraint is violated . This means that if the table has a NOT NULL constraint on a column that the INSERT does not provide, the statement fails before the UPDATE can run. The upsert cannot be used to “update only” a row that is missing a required column. This is a common surprise for developers who expect the UPDATE path to be taken when the row exists.

The SQL Server MERGE warning. Microsoft’s own documentation has warned about MERGE bugs and concurrency issues for years. The statement requires a semicolon terminator, and leaving it off produces error 10713 . The MERGE statement can produce incorrect results or primary key violations under concurrency without explicit locking hints . For single-row upserts, a TRY/CATCH with INSERT and UPDATE is often safer than MERGE. For full table synchronization, MERGE is the right tool.

a. PostgreSQL and SQLite: `INSERT ... ON CONFLICT`

The syntax has three parts: the INSERT statement, the conflict target, and the DO UPDATE or DO NOTHING action. The EXCLUDED pseudo-table references the values from the failed INSERT.

-- Insert a user or update the email if the username already exists
INSERT INTO users (username, email, last_login)
VALUES ('alice', 'alice@example.com', NOW())
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  last_login = EXCLUDED.last_login;

-- Insert or silently skip if the row exists
INSERT INTO users (username, email)
VALUES ('bob', 'bob@example.com')
ON CONFLICT (username) DO NOTHING;

The conflict target can be a single column, a composite of columns, a named constraint (ON CONFLICT ON CONSTRAINT users_username_key), or a partial index with a WHERE clause . The EXCLUDED table contains the row that would have been inserted. Column names without a prefix refer to the existing row.

A conditional update is possible with a WHERE clause on the DO UPDATE:

-- Only update if the new login is more recent
INSERT INTO users (username, email, last_login)
VALUES ('alice', 'alice@example.com', '2026-10-18')
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  last_login = EXCLUDED.last_login
WHERE users.last_login < EXCLUDED.last_login;

The RETURNING clause can show whether the row was inserted or updated, using the xmax system column: xmax = 0 means the row was inserted, xmax != 0 means it was updated .

b. MySQL: `INSERT ... ON DUPLICATE KEY UPDATE`

MySQL’s syntax does not name the conflict target. The update fires when any unique index or primary key is violated. The VALUES() function references the new values, but it is deprecated in MySQL 8.0.20 and later in favor of a row alias.

-- Insert or update; fires on any unique key violation
INSERT INTO users (username, email, login_count)
VALUES ('alice', 'alice@example.com', 1)
ON DUPLICATE KEY UPDATE
  email = VALUES(email),
  login_count = login_count + 1;

-- Modern syntax with row alias (MySQL 8.0.19+)
INSERT INTO users (username, email, login_count)
VALUES ('alice', 'alice@example.com', 1) AS new
ON DUPLICATE KEY UPDATE
  email = new.email,
  login_count = login_count + 1;

The affected-rows value tells you what happened: 1 if a new row was inserted, 2 if an existing row was updated, and 0 if the existing row was set to its current values . This is how the application can distinguish the two paths without a separate query.

The danger is the multiple-unique-index case. If the table has a unique index on email and another on username, and the INSERT conflicts on username but the UPDATE changes email to a value that already exists on a different row, the statement fails with a duplicate key error . The MySQL documentation recommends avoiding ON DUPLICATE KEY UPDATE on tables with multiple unique indexes for this reason.

c. SQL Server: `MERGE`

MERGE is the most general of the three. It joins a target table to a source table and applies different actions depending on whether a match is found.

-- Upsert a single row using a derived source table
MERGE INTO users AS target
USING (SELECT 'alice' AS username, 'alice@example.com' AS email) AS source
ON target.username = source.username
WHEN MATCHED THEN
  UPDATE SET email = source.email
WHEN NOT MATCHED BY TARGET THEN
  INSERT (username, email) VALUES (source.username, source.email);

The MERGE statement can also delete rows from the target that do not appear in the source, using WHEN NOT MATCHED BY SOURCE THEN DELETE. This makes it suitable for full synchronization, not just single-row upserts . The OUTPUT clause can return the action taken ($action) for each row, which is useful for auditing.

The concurrency caveat is serious. Without locking hints, the match phase runs with weak locks, and two concurrent sessions can both take the INSERT branch, causing a primary key violation . The fix is to add WITH (SERIALIZABLE) or WITH (UPDLOCK, HOLDLOCK) to the target table:

MERGE INTO users WITH (SERIALIZABLE) AS target
USING (SELECT 'alice' AS username, 'alice@example.com' AS email) AS source
ON target.username = source.username
WHEN MATCHED THEN UPDATE SET email = source.email
WHEN NOT MATCHED THEN INSERT (username, email) VALUES (source.username, source.email);

The SERIALIZABLE hint takes range locks on keys that do not exist, which prevents the concurrent insert race. The cost is reduced concurrency for other transactions that need the same range.

Complete Example Session

The following session demonstrates the same upsert in PostgreSQL, MySQL, and SQL Server, showing the syntax differences and the behavior of each.

-- ============================================
-- PART 1: POSTGRESQL — ON CONFLICT
-- ============================================
-- Table with a unique username
CREATE TABLE users_pg (
  username TEXT PRIMARY KEY,
  email TEXT NOT NULL,
  login_count INT DEFAULT 1
);

-- Insert alice or update if she exists
INSERT INTO users_pg (username, email, login_count)
VALUES ('alice', 'alice@example.com', 1)
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  login_count = users_pg.login_count + 1;

-- Run again: alice exists, so the update fires
INSERT INTO users_pg (username, email, login_count)
VALUES ('alice', 'alice.new@example.com', 1)
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  login_count = users_pg.login_count + 1;
-- Result: email updated, login_count = 2

-- ============================================
-- PART 2: MYSQL — ON DUPLICATE KEY UPDATE
-- ============================================
CREATE TABLE users_mysql (
  username VARCHAR(50) PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  login_count INT DEFAULT 1
);

INSERT INTO users_mysql (username, email, login_count)
VALUES ('alice', 'alice@example.com', 1)
ON DUPLICATE KEY UPDATE
  email = VALUES(email),
  login_count = login_count + 1;

-- Second run: update fires, affected rows = 2
INSERT INTO users_mysql (username, email, login_count)
VALUES ('alice', 'alice.new@example.com', 1)
ON DUPLICATE KEY UPDATE
  email = VALUES(email),
  login_count = login_count + 1;

-- ============================================
-- PART 3: SQL SERVER — MERGE
-- ============================================
CREATE TABLE users_sqlserver (
  username NVARCHAR(50) PRIMARY KEY,
  email NVARCHAR(100) NOT NULL,
  login_count INT DEFAULT 1
);

-- Add the SERIALIZABLE hint to prevent the concurrent insert race
MERGE INTO users_sqlserver WITH (SERIALIZABLE) AS target
USING (SELECT 'alice' AS username, 'alice@example.com' AS email) AS source
ON target.username = source.username
WHEN MATCHED THEN
  UPDATE SET email = source.email, login_count = target.login_count + 1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (username, email, login_count) VALUES (source.username, source.email, 1);

-- Second run: update fires
MERGE INTO users_sqlserver WITH (SERIALIZABLE) AS target
USING (SELECT 'alice' AS username, 'alice.new@example.com' AS email) AS source
ON target.username = source.username
WHEN MATCHED THEN
  UPDATE SET email = source.email, login_count = target.login_count + 1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (username, email, login_count) VALUES (source.username, source.email, 1);

The three parts demonstrate the same logical operation in each engine. PostgreSQL uses ON CONFLICT (username) and EXCLUDED. MySQL uses ON DUPLICATE KEY UPDATE and VALUES(). SQL Server uses MERGE with a derived source table and the SERIALIZABLE hint for concurrency safety.

Quick Reference

Syntax Comparison

Engine	Syntax	Conflict Target	New Value Reference
PostgreSQL	`INSERT ... ON CONFLICT`	Required for `DO UPDATE`	`EXCLUDED.column`
SQLite	`INSERT ... ON CONFLICT`	Required for `DO UPDATE`	`EXCLUDED.column`
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE`	Not supported	`VALUES(column)` or row alias
SQL Server	`MERGE`	Join condition	Source table columns

Concurrency Safety

Engine	Safe by Default	Fix
PostgreSQL	Yes	None needed
MySQL	Yes (single row)	None needed
SQL Server	No	`WITH (SERIALIZABLE)` or `WITH (UPDLOCK, HOLDLOCK)`

Multiple Unique Indexes

Engine	Behavior
PostgreSQL	Conflict target names the constraint; other conflicts fail
MySQL	Any unique violation triggers update; update that creates a new violation fails
SQL Server	Join condition determines match; no implicit unique constraint

When to Use Each

Situation	Choice
PostgreSQL/SQLite, single-row upsert	`ON CONFLICT`
MySQL, single unique index	`ON DUPLICATE KEY UPDATE`
SQL Server, single-row upsert	`MERGE` with hint, or `TRY/CATCH`
Full table synchronization	`MERGE` (SQL Server)

Best Practices

✅ Do This:

-- PostgreSQL: name the conflict target
INSERT INTO t (a, b) VALUES (1, 2) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;

-- SQL Server: add the serializable hint
MERGE INTO t WITH (SERIALIZABLE) AS target USING source ON ...;

-- MySQL: use a row alias instead of deprecated VALUES()
INSERT INTO t (a, b) VALUES (1, 2) AS new ON DUPLICATE KEY UPDATE b = new.b;

-- Sort batch rows by primary key to avoid deadlocks

❌ Don’t Do This:

-- MySQL: ON DUPLICATE KEY UPDATE on a table with multiple unique indexes
-- (the update may create a new conflict and fail)

-- SQL Server: MERGE without a locking hint
-- (concurrent inserts can cause primary key violations)

-- PostgreSQL: omit the conflict target with DO UPDATE
-- (syntax error; the target is required)

Common Pitfalls

Pitfall	Why It Happens	Fix
PostgreSQL upsert fails on NOT NULL	Insert path runs first, then update	Ensure all NOT NULL columns are provided
MySQL upsert fails with duplicate key	Multiple unique indexes, update creates new conflict	Use single unique index or handle in application
SQL Server MERGE duplicate key	No locking hint, concurrent inserts	Add `WITH (SERIALIZABLE)`
SQL Server syntax error	Missing semicolon after MERGE	End with `;`
Batch upsert deadlocks	Rows processed in different orders	Sort by primary key before batching

Real-World Examples

1. PostgreSQL upsert with EXCLUDED

INSERT INTO t (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

2. PostgreSQL DO NOTHING

INSERT INTO t (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO NOTHING;

3. PostgreSQL conditional update

ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE t.updated_at < EXCLUDED.updated_at;

4. MySQL upsert

INSERT INTO t (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name);

5. MySQL row alias (8.0.19+)

INSERT INTO t (id, name) VALUES (1, 'Alice') AS new ON DUPLICATE KEY UPDATE name = new.name;

6. SQL Server MERGE upsert

MERGE INTO t WITH (SERIALIZABLE) AS target USING (SELECT 1 AS id, 'Alice' AS name) AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

7. SQLite upsert

INSERT INTO t (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

8. PostgreSQL RETURNING insert vs update

INSERT INTO t (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name RETURNING id, (xmax = 0) AS inserted;

9. MySQL affected rows

1 = inserted, 2 = updated, 0 = no change

10. SQL Server OUTPUT action

MERGE INTO t AS target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... OUTPUT $action, inserted.id;

Visual

The Race Condition That Upsert Solves

┌──────────────────────────────────────────────┐
│  WITHOUT UPSERT                              │
│                                              │
│  T1: SELECT * FROM t WHERE id = 1 (no row)   │
│  T2: SELECT * FROM t WHERE id = 1 (no row)   │
│  T1: INSERT INTO t VALUES (1, 'a')           │
│  T2: INSERT INTO t VALUES (1, 'a')           │
│         │                                    │
│         ▼                                    │
│  T2: duplicate key violation                 │
│                                              │
│  WITH UPSERT                                 │
│  T1: INSERT ... ON CONFLICT DO UPDATE        │
│  T2: INSERT ... ON CONFLICT DO UPDATE        │
│         │                                    │
│         ▼                                    │
│  Both succeed atomically                     │
└──────────────────────────────────────────────┘

PostgreSQL Conflict Target

┌──────────────────────────────────────────────┐
│  ON CONFLICT (username)                      │
│         │                                    │
│         ▼                                    │
│  Which unique constraint?                    │
│  → the one on username                       │
│         │                                    │
│    ┌────┴────┐                               │
│    │         │                               │
│  conflict  no conflict                       │
│    │         │                               │
│    ▼         ▼                               │
│  UPDATE    INSERT                            │
│  with      new row                           │
│  EXCLUDED                                    │
└──────────────────────────────────────────────┘

SQL Server MERGE Race

┌──────────────────────────────────────────────┐
│  MERGE without hint                          │
│  T1: match phase (no row, weak lock)         │
│  T2: match phase (no row, weak lock)         │
│  T1: INSERT branch → succeeds                │
│  T2: INSERT branch → PK violation            │
│                                              │
│  MERGE WITH (SERIALIZABLE)                   │
│  T1: match phase takes range lock            │
│  T2: waits for range lock                    │
│  T1: INSERT branch → succeeds                │
│  T2: match phase sees the new row            │
│  T2: UPDATE branch → succeeds                │
└──────────────────────────────────────────────────────────────┘

MySQL Multiple Unique Index Danger

┌──────────────────────────────────────────────┐
│  Table: UNIQUE(username), UNIQUE(email)      │
│                                              │
│  INSERT ('alice', 'new@x.com')               │
│  ON DUPLICATE KEY UPDATE email = VALUES(email)│
│         │                                    │
│         ▼                                    │
│  Conflict on username → UPDATE fires         │
│  But 'new@x.com' already exists on another row│
│         │                                    │
│         ▼                                    │
│  Duplicate key violation on email            │
│  (statement fails)                           │
└──────────────────────────────────────────────┘

Summary

Item	Value
Upsert purpose	Atomic insert-or-update
PostgreSQL/SQLite	`INSERT ... ON CONFLICT`
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE`
SQL Server	`MERGE`
PostgreSQL new value	`EXCLUDED.column`
MySQL new value	`VALUES(column)` or row alias
SQL Server concurrency	Requires `WITH (SERIALIZABLE)`
PostgreSQL conflict target	Required for `DO UPDATE`
MySQL multiple unique indexes	Avoid `ON DUPLICATE KEY UPDATE`

Key takeaways:

The upsert eliminates the SELECT-then-INSERT race. Two concurrent transactions that both check for a row and then insert can collide. The native upsert makes the check and the write atomic .
PostgreSQL’s ON CONFLICT is the most precise. It names the conflict target, which means only the intended unique constraint triggers the update. A conflict on a different constraint fails instead of silently updating the wrong row .
MySQL’s ON DUPLICATE KEY UPDATE is the most dangerous on multiple unique indexes. It does not let you specify which conflict triggers the update, and an update that creates a new conflict on a different index fails .
SQL Server’s MERGE requires locking hints for concurrency safety. Without WITH (SERIALIZABLE) or WITH (UPDLOCK, HOLDLOCK), two concurrent sessions can both take the insert branch and one fails with a primary key violation .
The EXCLUDED table is the PostgreSQL way to reference new values. It contains the row that would have been inserted, which is what the update uses to overwrite the existing values .
MySQL’s VALUES() is deprecated. Use a row alias (AS new) and reference new.column in the update clause .
Batch upserts can deadlock. If two sessions process overlapping rows in different orders, they can form a circular wait. Sort by primary key before batching .

Remember: The three upsert syntaxes solve the same problem with different trade-offs. PostgreSQL’s ON CONFLICT is the safest and most precise because it names the conflict target and locks only the conflicting row. MySQL’s ON DUPLICATE KEY UPDATE is the simplest but the most fragile on tables with multiple unique indexes. SQL Server’s MERGE is the most general—it can insert, update, and delete in one statement—but it requires explicit locking hints to be safe under concurrency. The choice depends on the engine and the table’s unique constraints. The one rule that applies everywhere is to keep the batch sorted by primary key if you are upserting multiple rows, because the deadlock risk is real on all three engines.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!