MERGE ON FALSE in BigQuery

Merge statements are essential for crafting incremental datasets. They let us INSERT, UPDATE, and DELETE in a single command. 🛠️
Recently, I dived into an insightful blog post about the ON FALSE clause in merge statements. Ever heard of it?

Typical merge:

MERGE table1 AS target
USING table2 AS source ON table1.column = table2.column
WHEN MATCHED -- e.g., update target
WHEN NOT MATCHED BY source -- e.g., delete from target
WHEN NOT MATCHED BY target -- e.g., insert in target

But with ON FALSE in the merge_condition? BigQuery docs call it a "constant false predicate", perfect for atomic DELETEs on the target and INSERTs from a source. Essentially, a REPLACE operation.

I tested this on some data, especially after my previous post on Primary and Foreign Keys. The outcomes are looking super promising.

Testing that the expected changes happened.

Using the table version that has Primary Key and Foreign Key constraints has yielded even more impressive results.

Thanks for reading!

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.