DML

MERGE / UPSERT

Q: What's the difference between MERGE and UPSERT?

MERGE is the ANSI SQL term: explicit MERGE INTO target USING source ON ... syntax with WHEN MATCHED / WHEN NOT MATCHED branches. UPSERT is informal for "insert-or-update." INSERT ... ON CONFLICT (Postgres) and INSERT ... ON DUPLICATE KEY UPDATE (MySQL) are also upserts but use INSERT-style syntax instead of MERGE.

Insert-or-update in one statement. The engine matches incoming rows against an existing target on a key; matches get updated, non-matches get inserted. The standard pattern for incremental loads, change-data-capture, and any "make this table look like that table" job.

Syntax #

sql

-- ANSI MERGE (Snowflake / BigQuery / SQL Server / Oracle / DuckDB).
MERGE INTO target AS t
USING source AS s
   ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET t.col_1 = s.col_1, t.col_2 = s.col_2
WHEN NOT MATCHED THEN
  INSERT (id, col_1, col_2)
  VALUES (s.id, s.col_1, s.col_2);

-- Postgres / SQLite / DuckDB: ON CONFLICT.
INSERT INTO target (id, col_1, col_2)
VALUES (1, 'a', 'b'), (2, 'c', 'd')
ON CONFLICT (id) DO UPDATE SET
  col_1 = EXCLUDED.col_1,
  col_2 = EXCLUDED.col_2;

-- MySQL: ON DUPLICATE KEY UPDATE.
INSERT INTO target (id, col_1, col_2)
VALUES (1, 'a', 'b'), (2, 'c', 'd')
ON DUPLICATE KEY UPDATE
  col_1 = VALUES(col_1),
  col_2 = VALUES(col_2);

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL / SQLite / DuckDB	`INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col`	reference the proposed row via the `EXCLUDED` pseudo-table; `DO NOTHING` to skip duplicates
PostgreSQL 15+	`MERGE INTO target USING source ON ... WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ...`	new in PG15; most teams still prefer `ON CONFLICT` for OLTP
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col)`	triggers on any unique key violation, not only the intended one
SQL Server	`MERGE INTO target USING source ON ... WHEN MATCHED / WHEN NOT MATCHED BY TARGET / BY SOURCE`	richest MERGE (incl. delete branch), but MS itself warns about concurrency bugs, so `IF EXISTS` + `UPDATE` / `INSERT` is sometimes safer
Snowflake / BigQuery / Redshift / Oracle	`MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...`	canonical warehouse incremental-load pattern; atomic and set-based

Example #

Loading SQL editor...

Common variants #

sql

-- MERGE with a delete branch (SQL Server / Oracle / Snowflake / BigQuery).
MERGE INTO target AS t
USING source AS s
   ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

-- ON CONFLICT DO NOTHING — skip duplicates instead of updating (Postgres / SQLite / DuckDB).
INSERT INTO target (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO NOTHING;

-- ON CONFLICT with WHERE — update only when newer.
INSERT INTO target (id, value, updated_at) VALUES (1, 'new', CURRENT_TIMESTAMP)
ON CONFLICT (id) DO UPDATE SET
  value      = EXCLUDED.value,
  updated_at = EXCLUDED.updated_at
WHERE target.updated_at < EXCLUDED.updated_at;

Dialect notes #

Postgres added MERGE in 15. For incremental loads against earlier versions, INSERT ... ON CONFLICT (key) DO UPDATE SET ... is the idiomatic form, and is often preferred even on 15+.
MySQL has no MERGE. INSERT ... ON DUPLICATE KEY UPDATE is the equivalent, but it triggers on any unique key violation, not just the one you intend, which occasionally surprises.
SQL Server: full MERGE with MATCHED, NOT MATCHED BY TARGET, and NOT MATCHED BY SOURCE branches. Beware: MS itself recommends caution due to historical bugs around concurrent execution.
Snowflake / BigQuery / Redshift: MERGE is the canonical pattern for warehouse incremental loads. Atomic, set-based, no row-by-row execution.
dbt incremental models: under the hood they emit MERGE (warehouses) or INSERT ... ON CONFLICT (Postgres) — the same operation, abstracted.

FAQ #

What's the difference between MERGE and UPSERT?

MERGE is the ANSI SQL term: explicit MERGE INTO target USING source ON ... syntax with WHEN MATCHED / WHEN NOT MATCHED branches. UPSERT is informal for "insert-or-update." INSERT ... ON CONFLICT (Postgres) and INSERT ... ON DUPLICATE KEY UPDATE (MySQL) are also upserts but use INSERT-style syntax instead of MERGE.

Which engines support MERGE?

ANSI MERGE is supported on Snowflake, BigQuery, SQL Server, Oracle, DuckDB, Postgres (15+). Postgres typically uses INSERT ... ON CONFLICT instead. MySQL has no MERGE; use INSERT ... ON DUPLICATE KEY UPDATE.

How do I do an upsert in Postgres?

INSERT INTO target (id, value) VALUES (1, 'x') ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value. The EXCLUDED pseudo-table refers to the row that would have been inserted. Skip the update with ON CONFLICT (id) DO NOTHING.

What's a common bug with MERGE?

Duplicate keys in the source. If your source has two rows with id = 1, MERGE either errors or applies one of them (engine-dependent and not always documented). Always run SELECT id, COUNT() FROM source GROUP BY id HAVING COUNT() > 1 first to catch this.

Can I use MERGE for change-data-capture?

Yes — it's the canonical pattern for incremental loads in warehouses. MERGE matches incoming rows against the target on a key; updates existing rows; inserts new ones; some engines support a WHEN MATCHED AND s.is_deleted THEN DELETE branch. dbt incremental models emit MERGE under the hood on most warehouses.

Should I use MERGE in OLTP or OLAP?

OLAP / warehouses: MERGE is the canonical pattern for incremental loads. OLTP / transactional databases: smaller, more focused upserts (INSERT ... ON CONFLICT) usually fit better. OLTP MERGE works but is heavier syntax for what's usually a 1-row operation.

Warning

The match key must be unique on both sides. If your source has two rows with id = 1, MERGE either errors or applies one of them (engine-dependent and not always documented). Always run SELECT id, COUNT() FROM source GROUP BY id HAVING COUNT() > 1 against the source before a MERGE and either dedupe or fail loudly. Silent over-matches are a top-3 cause of incremental-load data quality bugs.