MERGE conditions, NULL keys and replacing a slice

A MERGE has more to say about which rows it touches. This lesson looks at a condition on a single clause, at keys that can be NULL, and at replacing part of a table.

How it works

The ON condition makes the pairs. Then each pair, and each row with no partner, goes to the first WHEN clause that fits it and whose AND condition is true. A row that no clause fits is left as it is.

Common mistake

A MERGE gives no message for a row that no clause fits or that a condition skipped. After a MERGE with conditions, read the rows the conditions were meant to leave alone.

Worked example

CREATE TEMP TABLE shelf AS
SELECT 'A1' AS slot, 10 AS units
UNION ALL SELECT 'B2', 4
UNION ALL SELECT NULL, 1;
MERGE shelf AS t
USING (
  SELECT 'A1' AS slot, -10 AS change
  UNION ALL SELECT 'B2', 3
  UNION ALL SELECT NULL, 2
) AS s
ON t.slot = s.slot
WHEN MATCHED AND t.units + s.change <= 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET units = t.units + s.change
WHEN NOT MATCHED THEN INSERT (slot, units) VALUES (s.slot, s.change);
SELECT slot, units FROM shelf ORDER BY slot, units;

Slot A1 would end at 0 units, so the first WHEN MATCHED clause applies and the row is deleted. The second clause never sees it.

Slot B2 fails the first condition, so the second clause updates it to 7.

The slot with no name has a NULL key on both sides, and NULL = NULL is not true. The batch row finds no partner, so WHEN NOT MATCHED inserts it as a second row with no name, and the old one keeps its 1 unit.

All BigQuery lessons