Combining Bigquery Delete and Insert Statements

Question

Is there a way in Bigquery to combine DELETE and INSERT statements into one

DELETE `my_project.my_dataset.demo` 
WHERE date = CURRENT_DATE()

INSERT INTO `my_project.my_dataset.demo` 
SELECT * FROM `my_project.my_dataset.my_source` 
WHERE date = CURRENT_DATE()

Any statement that can combine the above two DML into one ?

Answer 1

MERGE:

• https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

A MERGE statement is a DML statement that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically.

In the following example, all of the products in the NewArrivals table are replaced with values from the subquery. The INSERT clause does not specify column names for either the target table or the source subquery.

MERGE dataset.NewArrivals
USING (SELECT * FROM UNNEST([('microwave', 10, 'warehouse #1'),
                             ('dryer', 30, 'warehouse #1'),
                             ('oven', 20, 'warehouse #2')]))
ON FALSE
WHEN NOT MATCHED THEN
  INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN
  DELETE

Answer 2

I'm slightly modifying Felipe's answer to handle this use case. The only thing that needs to be changed is to add an extra clause to the WHEN NOT MATCHED statement:

MERGE `my_project.my_dataset.demo`
USING (SELECT * from `my_project.my_dataset.my_source` WHERE date=CURRENT_DATE())

ON 1=2  /* exactly the same as ON FALSE, but slightly clearer */
WHEN NOT MATCHED BY SOURCE AND date=CURRENT_DATE() THEN
  DELETE
WHEN NOT MATCHED BY TARGET THEN
  INSERT ROW

The key to understanding what's going on is that we're matching on the condition 1=2 -- that is, we will never match. But we can add extra conditions to our NOT MATCHED clause.

• WHEN NOT MATCHED BY SOURCE is every record in the existing table.
• WHEN NOT MATCHED BY SOURCE AND (whatever) is every record in the existing table that matches the "whatever" clause
• WHEN NOT MATCHED BY TARGET is every record in the incoming data