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.

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:

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;

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:

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;

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:

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.) QUALIFY with ROW_NUMBER() does it in one subquery:

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.

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

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:

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:

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:

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:

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:

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.

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.