Merge Statements BigQuery

QueryStash

Merge Statements BigQuery

The combination of insert and update in a merge statement simplifies data processing where we don't know if a record already exists.

The example below is merging into the dataset.insert_table using data from the dataset.source_table.

If value1 is a matched we update value2.
If value1 is not a match we insert a new record.

1MERGE `dataset.insert_table` a
2USING (
3  SELECT value1, value2
4 	FROM `dataset.source_table`
5) b ON b.value1 = a.value1
6
7WHEN MATCHED THEN
8	UPDATE SET value2 = b.value2
9
10WHEN NOT MATCHED BY TARGET THEN
11  INSERT (value1, value2)
12  VALUES (value1, value2)

MERGE `dataset.insert_table` a
USING (
  SELECT value1, value2
 	FROM `dataset.source_table`
) b ON b.value1 = a.value1

WHEN MATCHED THEN
	UPDATE SET value2 = b.value2

WHEN NOT MATCHED BY TARGET THEN
  INSERT (value1, value2)
  VALUES (value1, value2)

Public

bigquery

QueryStash

Created: Sep 16th 2020

QueryStash

Made with ❤️ by @Yekalb

Twitter Facebook Email

Resources

Blog Pricing Your First Query Search Your Stash

Examples

Merge Statements Persistent Functions Spike Detection QUALIFY Statement BigQuery ML Models

Stay up to date