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?
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!