求助解决BigQuery报错：UPDATE/MERGE必须为每个目标行匹配至多一个源行

Hey there, let's tackle this BigQuery error you're running into. The issue is straightforward: your source table sandbox.ref_sku_temp has duplicate Old_SKU values, and when BigQuery tries to run the UPDATE, some rows in sandbox.sellout are matching multiple rows from the source table. BigQuery can't decide which SKU value to use for the update, so it throws that error.

Step 1: Verify the Duplicate Data #

First, let's confirm which Old_SKU values are causing the problem. Run this query to find duplicates in your source table:

SELECT Old_SKU, COUNT(*) AS duplicate_count
FROM `sandbox.ref_sku_temp`
GROUP BY Old_SKU
HAVING COUNT(*) > 1

This will show you every Old_SKU that appears more than once, along with how many times it's repeated.

Step 2: Choose the Right Fix #

Now pick a solution based on what you find with the duplicate check:

Case 1: Duplicate Old_SKU entries have the same SKU value

If all the duplicate rows for a single Old_SKU have identical SKU values, you just need to remove duplicates from the source subquery. Use either DISTINCT or an aggregation function to ensure one Old_SKU maps to one SKU:

Option with DISTINCT:

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

Option with ANY_VALUE (good for ensuring uniqueness even if there are duplicates):

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

Case 2: Duplicate Old_SKU entries have different SKU values

If the duplicates have conflicting SKU values, you need to define a rule to pick the correct one. For example, if your table has an update_time field, you might want the most recent SKU:

UPDATE `sandbox.sellout` s 
SET s.SKU_Label = TRIM(SKU_TEMP.SKU) 
FROM (
  SELECT 
    Old_SKU, 
    SKU,
    -- Assign a row number, ordered by update time (newest first)
    ROW_NUMBER() OVER (PARTITION BY Old_SKU ORDER BY update_time DESC) AS row_num
  FROM `sandbox.ref_sku_temp`
) SKU_TEMP 
WHERE TRIM(SKU_TEMP.Old_SKU) = TRIM(s.SKU)
  AND SKU_TEMP.row_num = 1 -- Only keep the newest entry per Old_SKU

If you don't have a time field, you could sort by SKU length, alphabetical order, or another business rule that makes sense for your data.

内容的提问来源于stack exchange，提问作者user1404963