CODEWITHSUNDEEP
google-cloud-platformmergegoogle-bigqueryscheduled-tasks
John
John

Reputation: 59

Update unmatched rows using BigQuery MERGE function

I am trying to update a table using a scheduled query that uses the MERGE function to match and update rows. However, some of the rows that are in the table need to be deleted, as they have been deleted in other tables that the query references. However, because those rows no longer exist, they cannot be matched back to the existing rows in the table that is being updated. Is there someway to somehow do the opposite function of the MERGE function, as in only finding orders that are not matched to the scheduled query?

For example, this is how the table is currently being updated: enter image description here

This is how I would like for it to be updated: enter image description here

Upvotes: 1

Views: 2237

Answers (1)

Pentium10
Pentium10

Reputation: 207982

The not_matched_by_source_clause defines how to update or delete a row in the target table if that row does not match any row in the source table.

MERGE dataset.NewArrivals T
USING (SELECT * FROM dataset.NewArrivals WHERE warehouse <> 'warehouse #2') S
ON T.product = S.product
WHEN MATCHED AND T.warehouse = 'warehouse #1' THEN
  UPDATE SET quantity = T.quantity + 20
WHEN NOT MATCHED BY SOURCE THEN
  DELETE

more examples here on MERGE https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

Upvotes: 4

Related Questions