BigQuery Error: UPDATE/MERGE must match at most one source row for each target row

Using the SQL MERGE statement with BigQuery can be a huge boost to analytical transformation and data analysis processing but it does have a few gotchas. This error message “UPDATE/MERGE must match at most one source row for each target row” is typically seen when attempting the SQL MERGE statement into a target table and most often occurs when you have duplicate records in the source table you are using in the USING part of the MERGE statement.

One way to quickly assert what is going on in your data is to query the dataset/schema table looking for duplicate records. For example:

SELECT
  __ROW_MD5,
  COUNT(*)
FROM
  `SOURCE_DATASET.SOURCE_TABLE`
GROUP BY
  __ROW_MD5
HAVING
  COUNT(*) > 1 ;

The above SQL will do a basic check on the set of data in the table and identify if you have any rows with a count greater than one (1).

If that is the case then this is most likely the culprit to the UPDATE/MERGE must match at most one source row for each target row issue. Remove the duplicate records in the source table by adjusting your process or data accordingly and it should resolve your issue.

Feel free to read up other details about the SQL MERGE statement in BigQuery.