Skip to content
Tutorials Center
My learning

Topics

Lesson 61 of 125 ยท Changing Data

MERGE: Insert, Update and Delete in One Statement

Learn the SQL MERGE statement: sync a target table from a source with matched and not matched branches, deletes, and fallbacks for SQLite and MySQL.

Imagine a nightly job that receives a file of changes from another system. Some rows are new, some change existing rows, and some say "this record was removed". With the statements you know, that takes three passes: an INSERT for new keys, an UPDATE for known keys, and a DELETE for the removed ones. Each pass has to re-find which rows are which, and between the passes the table is in a half-updated state.

MERGE is the SQL standard's statement for doing all of it in one statement. You describe a target table, a source of changes and a join condition, then say what to do for rows that match, rows that exist only in the source, and (in some databases) rows that exist only in the target. The upsert lesson briefly showed MERGE as the SQL Server and Oracle way to upsert. This lesson goes through its full syntax and shows how to get the same result where MERGE doesn't exist.

The anatomy of MERGE

SQL
MERGE INTO target AS t
USING source AS s
   ON t.key = s.key
WHEN MATCHED THEN
   UPDATE SET t.col = s.col
WHEN NOT MATCHED THEN
   INSERT (key, col) VALUES (s.key, s.col);

Read it as a small program:

  1. USING names the source: a table, a view or a subquery in parentheses.
  2. ON is the join condition that decides which source rows correspond to which target rows.
  3. Each source row is then classified. If a target row matches, the WHEN MATCHED branch runs. If none does, WHEN NOT MATCHED runs.
  4. Each branch performs one action: UPDATE, DELETE or INSERT.

You can have several branches of the same kind if each has an extra condition, such as WHEN MATCHED AND s.op = 'D' THEN DELETE. The branches are tried in order and the first one whose condition holds wins, much like the arms of a CASE expression.

The source must not contain two rows that match the same target row. The standard says that is an error (PostgreSQL reports "MERGE command cannot affect row a second time", SQL Server and Oracle raise similar errors). Deduplicate the source first, for example with ROW_NUMBER() and a filter on rank 1.

A change feed with three kinds of rows

Say a catalog table product_catalog is kept in step with a feed feed. Each feed row carries an operation flag: U means upsert, D means delete. Here is the full MERGE as you would write it in PostgreSQL 15+, SQL Server or Oracle (syntax differs slightly, and the dialect section explains how):

SQL
-- Standard-style MERGE (read-only: SQLite and MySQL don't have it)
MERGE INTO product_catalog AS t
USING feed AS s
   ON t.sku = s.sku
WHEN MATCHED AND s.op = 'D' THEN
   DELETE
WHEN MATCHED THEN
   UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED AND s.op = 'U' THEN
   INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

Three rules sit in one statement: delete when the feed says so, update known products, insert unknown products. A D row for a sku that isn't in the catalog matches no branch, so it's simply ignored.

Running the same logic in SQLite

SQLite has no MERGE, but you can reproduce the exact behavior with three statements. The runner below does that on a small catalog. Note the order: delete first, then update, then insert, all inside one transaction so nobody sees the table half done.

SQL MERGE logic as three statements

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

The result has three rows: the lamp untouched, the notebook at its new price, and the new water bottle. The backpack is gone, and the unknown Z-9 delete changed nothing.

SQLite's UPDATE ... FROM (version 3.33+) is the stand-in for the matched branch. PostgreSQL, SQL Server and many others have the same form, which makes this three-statement recipe a decent fallback on any database without MERGE, such as MySQL.

The shorter way in SQLite and MySQL

If you only need update-or-insert, with no delete branch, the unique-key upsert is shorter and safer. SQLite uses INSERT ... ON CONFLICT DO UPDATE:

SQL The upsert shortcut

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

MySQL spells it INSERT ... ON DUPLICATE KEY UPDATE. Both are covered in the upsert lesson and in MySQL's own upsert lesson.

What the databases support

MERGE is part of the SQL standard (SQL:2003), but the engines differ in what they allow:

Database MERGE support
Oracle Since 9i. WHEN MATCHED THEN UPDATE ... DELETE WHERE ... (delete runs inside the matched branch, after the update); no AND condition on the WHEN line, filters go inside UPDATE ... WHERE.
SQL Server Since 2008. Adds WHEN NOT MATCHED BY SOURCE (target-only rows) and an OUTPUT clause. The statement must end with a semicolon.
PostgreSQL Since 15. Multiple WHEN branches with AND conditions, DO NOTHING. Version 17 added RETURNING and WHEN NOT MATCHED BY SOURCE.
MySQL None. Use ON DUPLICATE KEY UPDATE or the three-statement recipe.
SQLite None. Use ON CONFLICT or the three-statement recipe.

Target-only rows

Sometimes the feed is a full snapshot and anything missing from it should be removed. The standard has a clause for that, which SQL Server and PostgreSQL 17 implement:

SQL
-- SQL Server and PostgreSQL 17+ (read-only)
MERGE INTO product_catalog AS t
USING full_feed AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED BY TARGET THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

NOT MATCHED BY TARGET is the same as plain NOT MATCHED. In engines without BY SOURCE, you emulate the third branch with a separate DELETE ... WHERE NOT EXISTS (SELECT 1 FROM full_feed ...).

Gotchas

  • Concurrency. MERGE is a read followed by a write. Two sessions merging the same new key at once can both decide "not matched" and collide on the unique key. SQL Server needs WITH (HOLDLOCK) on the target; PostgreSQL's ON CONFLICT is the safer choice when you only need an upsert. Isolation and locking explains why.
  • Triggers. In SQL Server, triggers fire once per action type with all rows of that action, not once per source row.
  • Known issues. SQL Server's MERGE has a long list of reported bugs, and many teams use separate INSERT/UPDATE/DELETE statements in a transaction for critical paths. Test with your own volume.
  • Row counts. The affected-row count adds the inserted, updated and deleted rows together. Use OUTPUT or RETURNING where available to see which was which.
  • A NULL join key never matches, so a source row with a NULL key always lands in the "not matched" branch.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a MERGE, what decides which branch runs for a source row?

    Each source row is joined to the target with ON. A match runs WHEN MATCHED; no match runs WHEN NOT MATCHED. Branches are tried in order and AND conditions refine them.
  2. 2. What happens if two source rows match the same target row?

    The standard forbids affecting a target row twice. Deduplicate the source first.
  3. 3. Which database in this lesson has no MERGE statement?

    MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, or separate statements inside a transaction.
  4. 4. What does WHEN NOT MATCHED BY SOURCE refer to?

    BY SOURCE means the row exists in the target but not in the source. It is how a full snapshot feed deletes records that disappeared.

Summary

  • MERGE synchronizes a target table from a source in one statement, with WHEN MATCHED and WHEN NOT MATCHED branches that run UPDATE, DELETE or INSERT.
  • Branches are tried in order; extra AND conditions let you send different rows to different actions, such as deleting flagged rows.
  • The source must not hit the same target row twice.
  • Oracle, SQL Server and PostgreSQL 15+ support it; SQLite and MySQL don't, so use ON CONFLICT / ON DUPLICATE KEY or a delete-update-insert recipe inside a transaction.
  • MERGE is not automatically race-free; for plain upserts prefer the database's key-based upsert.

Next: Transactions, which make multi-statement recipes like the one above all-or-nothing.