Update unmatched rows using BigQuery MERGE function

Question

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:

This is how I would like for it to be updated:

Would the answer be to just use WHEN NOT MATCHED BY SOURCE THEN DELETE? — John

Pentium10 Pentium10 · Accepted Answer · 2020-01-29T22:15:26

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

Update unmatched rows using BigQuery MERGE function

1 Answers