MERGE in BigQuery

MERGE updates, inserts and deletes in one statement, which is how you upsert in BigQuery. It fails if a target row matches two source rows, can sync a table with WHEN NOT MATCHED BY SOURCE, and with ON FALSE replaces a whole partition atomically.

MERGE compares a target table with a source (a table or a query) row by row and, in one atomic statement, updates the rows that match, inserts the ones that are new, and deletes or flags the ones that disappeared. BigQuery has no INSERT ... ON CONFLICT or UPSERT, so MERGE is the way to apply a batch of changes. Because only two mutating statements run on a table at once, one MERGE per batch is also much better than many small UPDATEs. See INSERT, UPDATE and DELETE.

Upsert

The common case: update the rows that exist, insert the rest. The feed has a new price for apple and a new product, kiwi:

CREATE TEMP TABLE products (sku STRING, price NUMERIC, active BOOL);
INSERT INTO products VALUES ('apple', 1.20, TRUE), ('pear', 0.90, TRUE), ('plum', 2.00, TRUE);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC);
INSERT INTO price_feed VALUES ('apple', 1.10), ('kiwi', 0.50);
MERGE products AS t
USING price_feed AS s
ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET price = s.price
WHEN NOT MATCHED THEN
  INSERT (sku, price, active) VALUES (s.sku, s.price, TRUE);
SELECT * FROM products ORDER BY sku;

The ON condition decides which rows match. Each WHEN clause is tried in order, and the first one whose condition is true runs for that row.

Syncing a table with its source

WHEN NOT MATCHED BY SOURCE handles target rows that aren’t in the source. With all three clauses, the target ends up mirroring the feed. Here the products missing from the feed are marked inactive instead of deleted:

CREATE TEMP TABLE products (sku STRING, price NUMERIC, active BOOL);
INSERT INTO products VALUES ('apple', 1.20, TRUE), ('pear', 0.90, TRUE), ('plum', 2.00, TRUE);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC);
INSERT INTO price_feed VALUES ('apple', 1.10), ('kiwi', 0.50);
MERGE products AS t
USING price_feed AS s
ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET price = s.price
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, price, active) VALUES (s.sku, s.price, TRUE)
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET active = FALSE;
SELECT * FROM products ORDER BY sku;

WHEN NOT MATCHED is short for WHEN NOT MATCHED BY TARGET. A NOT MATCHED BY SOURCE clause can UPDATE or DELETE, but there is no source row, so it can only use target columns and constants.

Extra conditions on a clause

Add AND condition to a WHEN clause to act on some rows only. This only lowers prices, and only inserts products priced at 1 or more, so apple is lowered and kiwi is skipped:

CREATE TEMP TABLE products (sku STRING, price NUMERIC, active BOOL);
INSERT INTO products VALUES ('apple', 1.20, TRUE), ('pear', 0.90, TRUE), ('plum', 2.00, TRUE);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC);
INSERT INTO price_feed VALUES ('apple', 1.10), ('kiwi', 0.50);
MERGE products AS t
USING price_feed AS s
ON t.sku = s.sku
WHEN MATCHED AND s.price < t.price THEN
  UPDATE SET price = s.price
WHEN NOT MATCHED AND s.price >= 1 THEN
  INSERT (sku, price, active) VALUES (s.sku, s.price, TRUE);
SELECT * FROM products ORDER BY sku;

You can have several WHEN MATCHED clauses with different conditions, for example one that deletes and one that updates.

The source must not match a row twice

If two source rows match the same target row, BigQuery doesn’t choose one. The statement fails:

CREATE TEMP TABLE products (sku STRING, price NUMERIC);
INSERT INTO products VALUES ('apple', 1.20);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC);
INSERT INTO price_feed VALUES ('apple', 1.10), ('apple', 1.15);
MERGE products AS t
USING price_feed AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price;
SELECT * FROM products;

This happens often with feeds that deliver the same key more than once. Deduplicate the source in the USING query, keeping the latest row per key:

CREATE TEMP TABLE products (sku STRING, price NUMERIC);
INSERT INTO products VALUES ('apple', 1.20);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC, received_at TIMESTAMP);
INSERT INTO price_feed VALUES
  ('apple', 1.10, TIMESTAMP '2026-03-01 09:00:00'),
  ('apple', 1.15, TIMESTAMP '2026-03-01 10:00:00');
MERGE products AS t
USING (
  SELECT * FROM price_feed
  QUALIFY ROW_NUMBER() OVER (PARTITION BY sku ORDER BY received_at DESC) = 1
) AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price;
SELECT * FROM products;

See QUALIFY for the pattern. Duplicates only matter for rows that match. Two new rows with the same key are both inserted, which you may not want either.

INSERT ROW

INSERT ROW inserts all source columns in order, so you don’t list them. It needs the source to have exactly the target’s columns, in the same order:

CREATE TEMP TABLE products (sku STRING, price NUMERIC, active BOOL);
INSERT INTO products VALUES ('apple', 1.20, TRUE), ('pear', 0.90, TRUE), ('plum', 2.00, TRUE);
CREATE TEMP TABLE price_feed (sku STRING, price NUMERIC);
INSERT INTO price_feed VALUES ('apple', 1.10), ('kiwi', 0.50);
MERGE products AS t
USING price_feed AS s
ON t.sku = s.sku
WHEN NOT MATCHED THEN
  INSERT ROW;
SELECT * FROM products;

Use it when the source is a staging copy of the target. Otherwise list the columns.

Replacing a partition atomically

ON FALSE makes every row unmatched. Combined with a filtered NOT MATCHED BY SOURCE ... DELETE, this deletes one day and inserts its replacement in one statement, so readers never see the day half-loaded or missing:

CREATE TEMP TABLE daily_sales (day DATE, store STRING, units INT64);
INSERT INTO daily_sales VALUES
  ('2026-03-01', 'paris', 10), ('2026-03-02', 'paris', 12), ('2026-03-02', 'lyon', 4);
CREATE TEMP TABLE corrected (day DATE, store STRING, units INT64);
INSERT INTO corrected VALUES ('2026-03-02', 'paris', 13);
MERGE daily_sales AS t
USING corrected AS s
ON FALSE
WHEN NOT MATCHED BY SOURCE AND t.day = '2026-03-02' THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT ROW;
SELECT * FROM daily_sales ORDER BY day, store;

On a partitioned table, keep the delete condition on the partitioning column with a constant, as above, so that MERGE touches only that partition. A load job with WRITE_TRUNCATE to the partition decorator (table$20260302) does the same for files.

What MERGE costs

MERGE is billed for what it reads from the source plus what it reads from the target, and like UPDATE and DELETE it reads every column of the target partitions it may change. Whether BigQuery can skip target partitions depends on how you write it. We dry-ran these against a 2.25 MB target partitioned by day into 10 partitions, with a 100-row source for one day:

So:

• Put a constant filter on the target’s partitioning column in the ON clause. A filter that compares with the source, like t.day = s.day, can’t prune, because the values aren’t known when the statement is planned. If you know the batch’s date range, add it as a constant.
• Any WHEN NOT MATCHED BY SOURCE clause makes BigQuery compare the whole target with the source, so the whole target is read even with a partition filter elsewhere. The sync pattern above costs a full table read.
• The ON FALSE partition-replace pattern is the exception: there, the filter on the NOT MATCHED BY SOURCE clause does prune.

Querylab.io’s editor shows the dry-run estimate for a MERGE as you type, so you can see whether your ON filter prunes partitions before you run it (see cost limits).

Concurrency

Two MERGE statements that change the same partition at the same time conflict, and one is retried or fails. A MERGE that only inserts (no matched clauses run) doesn’t conflict with anything. To load from many parallel workers, have each one write to a staging table and run one MERGE from it.