Reputation: 1330
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 ?
Upvotes: 3
Views: 6011
Reputation: 821
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" clauseWHEN NOT MATCHED BY TARGET
is every record in the incoming dataUpvotes: 4
Reputation: 59375
MERGE
:
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
Upvotes: 4