MERGE a staged batch
A new batch of records usually has some rows that exist already and some that do not. MERGE handles both in one statement.
How it works
MERGE pairs the rows of a batch with the rows of a target table through the ON condition. Each pair, and each row with no partner, is handled by the first WHEN clause that fits it.
WHEN MATCHEDacts on target rows that have a partner in the batch.WHEN NOT MATCHEDmeans not matched by the target: it handles the batch rows that have no partner in the target, and it can only insert them.WHEN NOT MATCHED BY SOURCEacts on target rows that have no partner in the batch. Without a condition it reaches every such row, so limit it withAND condition.- The
ONcondition says what counts as the same row. The firstWHENclause whose condition holds is the one that applies, and all actions of oneMERGEsucceed or fail together. A target row can take only one new value, so it may have only one partner in the batch.UPDATE ... FROMfollows the same rule. - A
MERGEdoes what anUPDATEfollowed by anINSERTdoes, in one statement. If the second of two separate statements fails, the first has already changed the table.
Common mistake
A WHEN NOT MATCHED BY SOURCE clause without a condition deletes every target row the batch does not list. That is usually far more rows than the batch covers.
Worked example
CREATE TEMP TABLE example_stock AS
SELECT 1 AS sku, 10 AS units;
MERGE example_stock AS t
USING (SELECT 1 AS sku, 5 AS units UNION ALL SELECT 2, 8) AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET units = t.units + s.units
WHEN NOT MATCHED THEN INSERT (sku, units) VALUES (s.sku, s.units);
SELECT sku, units FROM example_stock ORDER BY sku;Sku 1 is in the table already, so MATCHED adds the 5 delivered units to its 10. Sku 2 is new, so it takes the INSERT branch.
The batch has one row for each sku, so every pair is unambiguous.