Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL MERGE statement

Reference for SQL MERGE: combine INSERT, UPDATE and DELETE in one statement driven by a source table, with dialect support and an SQLite upsert alternative.

MERGE compares a target table with a source (a table or query) using a join condition, and runs a different action depending on the outcome: update or delete the rows that match, insert the source rows that have no match. It is the standard SQL answer to "synchronize this table with that data".

MERGE is part of SQL:2003. PostgreSQL (15+), SQL Server and Oracle implement it; MySQL and SQLite do not. In SQLite use INSERT ... ON CONFLICT for insert-or-update and separate statements for deletes.

SQL
MERGE INTO inventory AS t
USING feed AS s
  ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET quantity = s.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, quantity) VALUES (s.sku, s.quantity);

Try it

SQL SQLite: the upsert that replaces a basic MERGE

Press Run (or Ctrl + Enter) to see the result here.

SQL SQLite: the extra WHEN clause as separate statements

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
MERGE INTO target [AS t]
USING source [AS s]
  ON join_condition
WHEN MATCHED [AND condition] THEN
  UPDATE SET column = expression [, ...]
  | DELETE
WHEN NOT MATCHED [AND condition] THEN
  INSERT (columns) VALUES (values);
Part Description
target The table being changed.
USING source A table, view or subquery that supplies the rows to apply.
ON The join condition that decides "matched". It should match at most one target row per source row.
WHEN MATCHED Runs for source rows with a partner in the target: UPDATE or DELETE.
WHEN NOT MATCHED Runs for source rows with no partner in the target: INSERT.
WHEN NOT MATCHED BY SOURCE SQL Server (and PostgreSQL 17+): target rows missing from the source, usually DELETEd.

Dialect differences

Dialect Support
SQLite No MERGE. Use INSERT ... ON CONFLICT DO UPDATE and separate DELETE.
PostgreSQL MERGE from 15; NOT MATCHED BY SOURCE and RETURNING from 17. For single-row work ON CONFLICT is often simpler and safer under concurrency.
MySQL No MERGE. Use INSERT ... ON DUPLICATE KEY UPDATE.
SQL Server Full MERGE, ended with a semicolon. Has a history of concurrency bugs; many teams prefer explicit UPDATE and INSERT under a lock hint. Supports OUTPUT.
Oracle Full MERGE; UPDATE can carry a DELETE WHERE clause.

Usage notes

  • Each target row may be matched by at most one source row. Otherwise SQL Server and Oracle raise an error; the result would be ambiguous.
  • Deduplicate the source first (for example with GROUP BY or ROW_NUMBER()).
  • Everything happens in one statement, but under heavy concurrency MERGE can still hit unique-key races unless the isolation level or lock hints prevent them.
  • Conditions on WHEN (WHEN MATCHED AND s.quantity = 0 THEN DELETE) let one statement handle several outcomes.
  • Check the dialect's documentation before using MERGE in a portable codebase: the clauses differ in small ways.
  • Test with a SELECT that joins source and target, to see which rows will match.