Sync a Target Table From a Source With MERGE

Use `MERGE` to insert new rows, update matching ones, and delete rows that should be removed — all in a single statement against a source table.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/2 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Apply updates and deletions from a staging table in one pass

The source table has three rows. Row id=1 matches a target row and remove=0, so the second WHEN MATCHED clause fires and updates the name to 'Widget Pro' and price to 15. Row id=2 also matches and remove=1, so the first WHEN MATCHED AND s.remove clause fires and deletes the target row. Row id=3 has no match in products, so WHEN NOT MATCHED fires and inserts it. The final SELECT shows two rows: the updated Widget Pro and the newly inserted Doohickey. SQL Server uses WHEN NOT MATCHED BY TARGET to distinguish source rows with no target match from WHEN NOT MATCHED BY SOURCE (target rows with no source match). PostgreSQL uses the simpler WHEN NOT MATCHED. In both engines the WHEN clauses fire in order and only the first matching one applies.

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50), price INT);

CREATE TABLE product_updates (id INT, name VARCHAR(50), price INT, remove BIT);

INSERT INTO
  products
VALUES
  (1, 'Widget', 10),
  (2, 'Gadget', 20);

INSERT INTO
  product_updates
VALUES
  (1, 'Widget Pro', 15, 0),
  (2, 'Gadget', 20, 1),
  (3, 'Doohickey', 8, 0);

Validated query

SQL

MERGE products AS t USING product_updates AS s ON t.id = s.id WHEN MATCHED
AND s.remove = 1 THEN DELETE WHEN MATCHED THEN
UPDATE
SET
  t.name = s.name,
  t.price = s.price WHEN NOT MATCHED BY TARGET THEN INSERT (id, name, price)
VALUES
  (s.id, s.name, s.price);

SELECT
  id,
  name,
  price
FROM
  products
ORDER BY
  id;

Expected result

id	name	price
1	Widget Pro	15
3	Doohickey	8

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50), price INT);

CREATE TABLE product_updates (
  id INT,
  name VARCHAR(50),
  price INT,
  remove BOOLEAN
);

INSERT INTO
  products
VALUES
  (1, 'Widget', 10),
  (2, 'Gadget', 20);

INSERT INTO
  product_updates
VALUES
  (1, 'Widget Pro', 15, FALSE),
  (2, 'Gadget', 20, TRUE),
  (3, 'Doohickey', 8, FALSE);

Validated query

SQL

MERGE INTO products AS t USING product_updates AS s ON t.id = s.id WHEN MATCHED
AND s.remove THEN DELETE WHEN MATCHED THEN
UPDATE
SET
  name = s.name,
  price = s.price WHEN NOT MATCHED THEN INSERT (id, name, price)
VALUES
  (s.id, s.name, s.price);

SELECT
  id,
  name,
  price
FROM
  products
ORDER BY
  id;

Expected result

id	name	price
1	Widget Pro	15
3	Doohickey	8

SQL Server and PostgreSQL 15+ support MERGE with slightly different clause syntax. MySQL, MariaDB, and SQLite have no equivalent.

Useful when

Where this command helps.

synchronizing a staging table into a live production table as part of an ETL pipeline
refreshing a cache or summary table from a source-of-truth table in one atomic pass

Explanation

What the command is doing.

The MERGE statement compares each row in a source table or subquery against the target table using an ON condition, then applies actions based on whether a match was found. WHEN MATCHED THEN UPDATE handles rows that exist in both tables; WHEN MATCHED AND condition THEN DELETE removes matching rows that meet a deletion criterion; WHEN NOT MATCHED THEN INSERT adds rows present in the source but missing from the target. Each WHEN clause is evaluated in order and only the first matching clause fires per source row. SQL Server requires BY TARGET in WHEN NOT MATCHED BY TARGET to distinguish inserts from the WHEN NOT MATCHED BY SOURCE form that deletes target rows with no source match. PostgreSQL 15+ uses the simpler WHEN NOT MATCHED. MySQL and MariaDB do not support MERGE; the closest alternative is INSERT ... ON DUPLICATE KEY UPDATE, which handles upserts but cannot include a delete leg in the same statement.

Sync a Target Table From a Source With MERGE

Apply updates and deletions from a staging table in one pass

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.