How to Use MERGE in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
MERGE is the one-statement upsert: rows from the source that match ON update the target, rows that do not match insert into it, and WHEN NOT MATCHED BY SOURCE can delete what the source no longer has.
MERGE dim_customers AS target
USING staging_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
INSERT (customer_id, name, email)
VALUES (source.customer_id, source.name, source.email);Full sync: delete what the source dropped
WHEN NOT MATCHED BY SOURCE matches target rows with no counterpart in the source, which makes MERGE a complete mirror operation instead of just an upsert. The whole statement is atomic - readers never see a half-applied sync.
MERGE dim_customers AS target
USING staging_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
INSERT (customer_id, name, email)
VALUES (source.customer_id, source.name, source.email)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;Deduplicate the source first
MERGE fails at runtime if several source rows match the same target row, because the update would be ambiguous. Staging data with repeated keys needs a QUALIFY ROW_NUMBER() = 1 pass (or a GROUP BY) before it can be merged.
MERGE dim_customers AS target
USING (
SELECT *
FROM staging_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
INSERT (customer_id, name, email)
VALUES (source.customer_id, source.name, source.email);Conditions on a match
Each WHEN clause takes an extra AND condition, so you can update only rows that actually changed or route hard deletes on a flag column.
MERGE dim_customers AS target
USING staging_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.is_deleted THEN
DELETE
WHEN MATCHED THEN
UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
INSERT (customer_id, name, email)
VALUES (source.customer_id, source.name, source.email);Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free