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

Question

Just wondering if someone could help with the following error:

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

My query is as below:

UPDATE `sandbox.sellout` s
SET s.SKU_Label = TRIM(SKU_TEMP.SKU)
FROM (SELECT SKU, Old_SKU FROM `sandbox.ref_sku_temp`) SKU_TEMP
WHERE TRIM(SKU_TEMP.Old_SKU) = TRIM(s.SKU)

Answer 1

If a row in the table to be updated joins with more than one row from the FROM clause, then the query generates the following runtime error: UPDATE/MERGE must match at most one source row for each target row. Data Manipulation Language Syntax.

Answer 2

It occurs because the target table of the BigQuery contains duplicated row(w.r.t you are joining). If a row in the table to be updated joins with more than one row from the FORM clause, then BigQuery returns this error:

Solution

• Remove the duplicated rows from the target table and perform the UPDATE/MERGE operation
• Define Primary key in BigQuery target table to avoid data redundancy