What is Upsert (MERGE)?

How it works

A MERGE has three parts: a target table, a source (a staging table, a query or a batch of changes) and a join condition on the business key. For each source row, the engine decides which clause applies:

• WHEN MATCHED – the key already exists in the target: update it, or delete it (for CDC deletes).
• WHEN NOT MATCHED – the key is new: insert it.
• WHEN NOT MATCHED BY SOURCE (Delta Lake, SQL Server, PostgreSQL 17+) – the target row is missing from the source: delete or flag it, for full-snapshot sources.

Each clause can carry an extra condition, such as AND t.price IS DISTINCT FROM s.price, so unchanged rows are not rewritten. That keeps audit columns honest and, on lakehouse tables, avoids rewriting files for nothing.

An upsert keyed on a business key is naturally idempotent: running the same batch twice leaves the same result, which is why it is the default load pattern for incremental and CDC pipelines. Other spellings of the same idea are PostgreSQL’s INSERT … ON CONFLICT DO UPDATE, MySQL’s ON DUPLICATE KEY UPDATE and Delta Lake’s DeltaTable.merge().

Example

P2’s price drops and P3 is new; P1 is untouched. Running the same MERGE a second time changes nothing (PostgreSQL 16).

CREATE TABLE dim_product (product_id text PRIMARY KEY, name text, price numeric(8,2));
INSERT INTO dim_product VALUES ('P1', 'Mouse', 499.00), ('P2', 'Keyboard', 1299.00);
CREATE TABLE stg_product (product_id text, name text, price numeric(8,2));
INSERT INTO stg_product VALUES ('P2', 'Keyboard', 1199.00), ('P3', 'Monitor', 8999.00);

MERGE INTO dim_product t
USING stg_product s ON t.product_id = s.product_id
WHEN MATCHED AND t.price IS DISTINCT FROM s.price THEN
  UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED THEN
  INSERT (product_id, name, price) VALUES (s.product_id, s.name, s.price);

SELECT * FROM dim_product ORDER BY product_id;

 product_id |   name   |  price
------------+----------+---------
 P1         | Mouse    |  499.00
 P2         | Keyboard | 1199.00
 P3         | Monitor  | 8999.00

Common mistakes

• Feeding a source with two rows for the same key: most engines raise an error (“more than one source row matched”), so deduplicate to the latest change per key first.
• Joining on a column that is not unique in the target, which updates several rows per source row.
• Updating every matched row even when nothing changed, which inflates write cost and change feeds.
• Forgetting deletes: a plain upsert never removes rows that disappeared from the source.