# 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.

Source: https://docs.querylab.io/bigquery/merge/

Updated: 2026-10-14

`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 `UPDATE`s. See [INSERT, UPDATE and DELETE](https://docs.querylab.io/bigquery/dml/).

## 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`:

**update apple, insert kiwi**

```sql
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;
```

Result in BigQuery (4 rows):

| sku | price | active |
| --- | --- | --- |
| apple | 1.1 | true |
| kiwi | 0.5 | true |
| pear | 0.9 | true |
| plum | 2 | true |

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:

**flag products the feed no longer has**

```sql
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;
```

Result in BigQuery (4 rows):

| sku | price | active |
| --- | --- | --- |
| apple | 1.1 | true |
| kiwi | 0.5 | true |
| pear | 0.9 | false |
| plum | 2 | false |

`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:

**WHEN MATCHED AND ..., WHEN NOT MATCHED AND ...**

```sql
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;
```

Result in BigQuery (3 rows):

| sku | price | active |
| --- | --- | --- |
| apple | 1.1 | true |
| pear | 0.9 | true |
| plum | 2 | true |

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:

**two prices for apple in the feed**

```sql
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;
```

BigQuery rejects this query:

```text
Query error: UPDATE/MERGE must match at most one source row for each target row at [5:1]
```

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:

**keep the latest price per SKU**

```sql
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;
```

Result in BigQuery (1 row):

| sku | price |
| --- | --- |
| apple | 1.15 |

See [QUALIFY](https://docs.querylab.io/bigquery/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:

**a two-column source into a three-column table**

```sql
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;
```

BigQuery rejects this query:

```text
Query error: Inserted row has wrong column count; Has 2, expected 3 at [9:10]
```

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:

**replace March 2 with corrected data**

```sql
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;
```

Result in BigQuery (2 rows):

| day | store | units |
| --- | --- | --- |
| 2026-03-01 | paris | 10 |
| 2026-03-02 | paris | 13 |

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:

| `MERGE` form                                                                                          | Bytes processed           |
| ----------------------------------------------------------------------------------------------------- | ------------------------- |
| `ON t.id = s.id`, `WHEN MATCHED`                                                                      | 2.25 MB, the whole target |
| `ON t.id = s.id AND t.day = '2026-01-03'`, `WHEN MATCHED`                                             | 232 KB, one partition     |
| `ON t.id = s.id AND t.day = s.day`, `WHEN MATCHED`                                                    | 2.25 MB, the whole target |
| the constant `ON` filter plus `WHEN NOT MATCHED ... INSERT`                                           | 232 KB, one partition     |
| the constant `ON` filter plus `WHEN NOT MATCHED BY SOURCE AND t.day = '2026-01-03' ... DELETE`        | 2.25 MB, the whole target |
| `ON FALSE`, `NOT MATCHED BY SOURCE AND t.day = '2026-01-03' ... DELETE`, `NOT MATCHED ... INSERT ROW` | 232 KB, one partition     |

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](#syncing-a-table-with-its-source) 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](https://docs.querylab.io/writing-sql/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.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [DML syntax, MERGE statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)

- [Transform data with DML](https://cloud.google.com/bigquery/docs/data-manipulation-language)

- [Update partitioned table data using DML](https://cloud.google.com/bigquery/docs/using-dml-with-partitioned-tables)