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.

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.

All BigQuery lessons