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.
WHEN MATCHED AND condition THEN ...applies to a pair only when the condition is true. If the condition is false orNULL, BigQuery tries the nextWHEN MATCHEDclause. Only the first clause that applies runs.- A pair that fits no clause is left alone, and BigQuery says nothing about it. A clause with no condition fits every pair that reaches it, so a clause written after it never runs.
- The
ONcondition pairs two rows only when it is true.NULL = NULLisNULL, so two rows with aNULLkey are not a pair. The batch row counts as new, andWHEN NOT MATCHEDinserts it beside the target row that has the same missing key. - A key test pairs two
NULLkeys when it is also true for a pair where both sides areNULL.a IS NOT DISTINCT FROM bdoes that. As the onlyONcondition it plans a cross join. That compares every source row with every target row. A plain equality with the both-NULLcase added throughORpairs them and lets BigQuery plan a hash join. ACOALESCEto a made-up value also pairs aNULLwith a real row that holds that value. - Once
NULLkeys pair, a target row can have two batch partners again. Two batch rows with aNULLkey and one target row with aNULLkey fail with the error about a target row matching two source rows. ON FALSEpairs nothing, so every target row isNOT MATCHED BY SOURCEand every source row isNOT MATCHED. OneMERGEcan then act on both sides at once.- A reader sees the table as it was before the
MERGEor as it is after it. ANOT MATCHED BY SOURCEcondition reads the target alone, because there is no source row.INSERT ROWwrites the source columns in order without listing them, so the source needs the columns of the target in the same order.
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.