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.
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
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 BYorROW_NUMBER()). - Everything happens in one statement, but under heavy concurrency
MERGEcan 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
MERGEin a portable codebase: the clauses differ in small ways. - Test with a
SELECTthat joins source and target, to see which rows will match.