# UPDATE/MERGE must match at most one source row for each target row

> A row of the table being updated matched more than one row of the source, so BigQuery has no way to choose which value to write. The source has duplicate keys. Reduce it to one row per key with QUALIFY ROW_NUMBER or GROUP BY, then run the statement again.

Source: https://docs.querylab.io/bigquery-errors/update-merge-must-match-at-most-one-source-row/

Updated: 2026-10-30

```text
UPDATE/MERGE must match at most one source row for each target row
```

## What BigQuery means

A `MERGE` or an `UPDATE ... FROM` joins the table you change (the target) to a source. For each target row it needs one source row to take values from. Here, one target row matched two or more source rows. The statement could write either value, BigQuery will not pick one, and nothing is changed.

The cause is almost always duplicate keys in the source. Two rows with the same `id` both match the one target row with that `id`. Rows that are identical in every column count too.

It is a runtime error. The statement is valid, and it only fails when the data has the duplicate. The check is on the join, not on the columns you set, so it fails even when both source rows carry the same value.

## The smallest query that reproduces it

The error needs a table to change, so the script below makes two temp tables. Only the script can read them, and BigQuery deletes them within 24 hours:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status;
CREATE TEMP TABLE source AS SELECT 1 AS id, "a" AS status UNION ALL SELECT 1, "b";
MERGE target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status;
```

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

`[3:1]` is the start of the `MERGE` statement, not of the join. In a script BigQuery adds `Query error:` and the position of the failing statement. The `UPDATE ... FROM` form fails the same way:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status;
CREATE TEMP TABLE source AS SELECT 1 AS id, "a" AS status UNION ALL SELECT 1, "b";
UPDATE target AS t
SET status = s.status
FROM source AS s
WHERE t.id = s.id;
```

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

The documentation puts it this way for `UPDATE`: a target row can join with at most one row of the `FROM` clause.

## The fix

First find the keys that repeat. This script has a `version` column so that there is a reason to prefer one row:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status;
CREATE TEMP TABLE source AS SELECT 1 AS id, "a" AS status, 1 AS version UNION ALL SELECT 1, "b", 2;
SELECT id, COUNT(*) AS n FROM source GROUP BY id HAVING COUNT(*) > 1;
```

This returns `id = 1, n = 2`. Then decide which row should win, and make the source hold only that row.

**Keep the latest row per key.** (More on the statement itself: [MERGE](https://docs.querylab.io/bigquery/merge/).) `QUALIFY` with `ROW_NUMBER()` does it in one subquery:

```sql
MERGE target AS t
USING (
  SELECT * FROM source
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) = 1
) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status;
```

After this, `target` holds `status = b`, the row with the higher `version`. The `ORDER BY` inside the window decides the winner. Without one the winner is arbitrary, so always order by something that means "the row I want". See [QUALIFY](https://docs.querylab.io/bigquery/qualify/).

**Collapse the rows with `GROUP BY`.** Use this when one value per key is enough, such as any one, the largest or the total:

```sql
MERGE target AS t
USING (SELECT id, ANY_VALUE(status) AS status FROM source GROUP BY id) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status;
```

`ANY_VALUE` returns one of the values, with no promise which. If the column should be the largest, use `MAX`. If it should be the total, use `SUM`.

**The same for `UPDATE ... FROM`.** Put the reduced query in the `FROM` clause:

```sql
UPDATE target AS t
SET status = s.status
FROM (
  SELECT * FROM source
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) = 1
) AS s
WHERE t.id = s.id;
```

**Narrow the join.** If one of the source rows is the right one for a reason you can state, put that reason in the `ON` clause. `ON t.id = s.id AND s.status = "a"` matched one row and ran. This only works when the extra condition really leaves one row per key.

Check the fix on the data you have. A key that repeats in a way you did not expect is a sign that the source itself is wrong, and then removing the extra rows hides a problem.

## The variants of the message

The text is the same for `MERGE` and for `UPDATE`. The only variation is the prefix:

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

The second form is what a multi-statement query returns. The documentation quotes the first.

## Queries that look wrong but run

**A duplicate source with `WHEN MATCHED THEN DELETE`.** BigQuery checks only when a `WHEN MATCHED` clause does an `UPDATE`. This ran and deleted the one target row:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status;
CREATE TEMP TABLE source AS SELECT 1 AS id, "a" AS status UNION ALL SELECT 1, "b";
MERGE target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN DELETE;
```

**Duplicates that never match.** A source with two rows for a key that is not in the target, and only a `WHEN NOT MATCHED THEN INSERT` clause, inserts both rows. No target row matched anything:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status;
CREATE TEMP TABLE source AS SELECT 2 AS id, "a" AS status UNION ALL SELECT 2, "b";
MERGE target AS t
USING source AS s
ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, status) VALUES (s.id, s.status);
```

**Duplicates in the target.** The rule is about source rows per target row. Two target rows with the same key and one source row are fine, and both are updated:

```sql
CREATE TEMP TABLE target AS SELECT 1 AS id, "old" AS status UNION ALL SELECT 1, "older";
CREATE TEMP TABLE source AS SELECT 1 AS id, "a" AS status;
MERGE target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status;
```

## Different errors that look like this one

**`Scalar subquery produced more than one element`.** The same kind of problem in a subquery used as a value. See [Scalar subquery produced more than one element](https://docs.querylab.io/bigquery-errors/scalar-subquery-produced-more-than-one-element/).

## In Querylab.io

The editor does not mark this error, because it depends on the rows in the tables. For the `MERGE` and `UPDATE ... FROM` scripts above, it showed only a warning for each temp table that is never dropped. The error comes back from BigQuery when the script runs.

There is no fix button for it. Reduce the source by hand, as above.

## Sources

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

- [DML statements, UPDATE using joins](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax)

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

- [Query syntax, QUALIFY clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)