BigQuery MERGE Statement

MERGE is an UPSERT operation, that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically

MERGE Statement example

\nMERGE dataset.Inventory T\nUSING dataset.NewArrivals S\nON T.product = S.product\nWHEN MATCHED THEN\n  UPDATE SET quantity = T.quantity + S.quantity\nWHEN NOT MATCHED THEN\n  INSERT (product, quantity) VALUES(product, quantity)\n

\nMERGE dataset.Inventory T\nUSING (SELECT product, quantity, state FROM dataset.NewArrivals t1 JOIN dataset.Warehouse t2 ON t1.warehouse = t2.warehouse) S\nON T.product = S.product\nWHEN MATCHED AND state = 'CA' THEN\n  UPDATE SET quantity = T.quantity + S.quantity\nWHEN MATCHED THEN\n  DELETE\n

Syntax reference

MERGE [INTO] target_name [[AS] alias]\nUSING source_name\nON merge_condition\n{ when_clause } +\n\nwhen_clause ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause\n\nmatched_clause ::= WHEN MATCHED [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }\n\nnot_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND search_condition ] THEN merge_insert_clause\n\nnot_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }\n\nmerge_condition ::= bool_expression\n\nsearch_condition ::= bool_expression\n\nmerge_update_clause ::= UPDATE SET update_item [, update_item]*\nupdate_item ::= column_name = expression\n\nmerge_delete_clause ::= DELETE\n\nmerge_insert_clause ::= INSERT [(column_1 [, ..., column_n ])] input\n\ninput ::= VALUES (expr_1 [, ..., expr_n ]) | ROW\n\nexpr ::= expression | DEFAULT