BigQuery中UPDATE子查询用UNION ALL报错的解决方法

问题场景 #

我在BigQuery中通过CLI执行UPDATE语句更新表，初始查询运行正常，但添加UNION ALL后执行失败，报错信息如下：

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

初始正常的查询 #

bq query --use_legacy_sql=false "UPDATE data_set.table_to_update A 
                                SET D_A_AMOUNT = B.D_A_AMOUNT, A_FEE = B.A_FEE, 
                                O_T_AMOUNT = B.O_T_AMOUNT, U_FEE = B.U_FEE, 
                                UPDATED_DATETIME = B.UPDATED_DATETIME, UPDATED_BY = B.UPDATED_BY 
                                    FROM data_set.table_to_update C 
                                    INNER JOIN ( 
                                    SELECT CAST(131.27 AS NUMERIC) AS D_A_AMOUNT, CAST(20.66 AS NUMERIC) AS A_FEE, 
                                    '12345' AS TRANSACTION_KEY, CAST(145871.0 AS NUMERIC) AS O_T_AMOUNT, 
                                    CAST(131.27 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME, 
                                    'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY ) B 
                                    ON C.TRANSACTION_KEY = B.TRANSACTION_KEY  WHERE C.L_IDENTITY = 'P_S_US';"

添加UNION ALL后的错误查询 #

bq query --use_legacy_sql=false "UPDATE data_set.table_to_update A 
                                SET D_A_AMOUNT = B.D_A_AMOUNT, A_FEE = B.A_FEE, 
                                O_T_AMOUNT = B.O_T_AMOUNT, U_FEE = B.U_FEE, 
                                UPDATED_DATETIME = B.UPDATED_DATETIME, UPDATED_BY = B.UPDATED_BY 
                                    FROM data_set.table_to_update C 
                                    INNER JOIN ( 
                                    SELECT CAST(131.27 AS NUMERIC) AS D_A_AMOUNT, CAST(20.66 AS NUMERIC) AS A_FEE, 
                                    '12345' AS TRANSACTION_KEY, CAST(145871.0 AS NUMERIC) AS O_T_AMOUNT, 
                                    CAST(131.27 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME, 
                                    'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY 
                                    UNION ALL  
                                    SELECT CAST(134.19 AS NUMERIC) AS D_A_AMOUNT, CAST(21.31 AS NUMERIC) AS A_FEE, 
                                    '987654232' AS TRANSACTION_KEY, CAST(149112.0 AS NUMERIC) AS O_T_AMOUNT, 
                                    CAST(134.19 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME, 
                                    'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY) B 
                                    ON C.TRANSACTION_KEY = B.TRANSACTION_KEY  WHERE C.L_IDENTITY = 'P_S_US';"

错误原因 #

BigQuery的UPDATE语句要求目标表的每一行最多只能匹配到源数据中的一行。你的查询中存在不必要的自关联（同时引用data_set.table_to_update作为A和C），这种写法可能导致目标行和源数据产生一对多的匹配关系，触发报错。另外如果UNION ALL后的子查询中存在重复的TRANSACTION_KEY，也会导致同样的问题。

修正方案 #

方案1：简化查询，移除不必要的自关联 #

直接让目标表和UNION ALL后的子查询关联，避免重复匹配：

bq query --use_legacy_sql=false "UPDATE data_set.table_to_update A
SET D_A_AMOUNT = B.D_A_AMOUNT, A_FEE = B.A_FEE,
O_T_AMOUNT = B.O_T_AMOUNT, U_FEE = B.U_FEE,
UPDATED_DATETIME = B.UPDATED_DATETIME, UPDATED_BY = B.UPDATED_BY
FROM (
    SELECT CAST(131.27 AS NUMERIC) AS D_A_AMOUNT, CAST(20.66 AS NUMERIC) AS A_FEE,
    '12345' AS TRANSACTION_KEY, CAST(145871.0 AS NUMERIC) AS O_T_AMOUNT,
    CAST(131.27 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME,
    'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY
    UNION ALL  
    SELECT CAST(134.19 AS NUMERIC) AS D_A_AMOUNT, CAST(21.31 AS NUMERIC) AS A_FEE,
    '987654232' AS TRANSACTION_KEY, CAST(149112.0 AS NUMERIC) AS O_T_AMOUNT,
    CAST(134.19 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME,
    'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY
) B
ON A.TRANSACTION_KEY = B.TRANSACTION_KEY 
WHERE A.L_IDENTITY = 'P_S_US';"

方案2：确保源数据无重复（针对可能存在重复TRANSACTION_KEY的情况） #

如果UNION ALL后的子查询中可能存在重复的TRANSACTION_KEY，可以通过窗口函数筛选唯一行，确保每个TRANSACTION_KEY只对应一条更新数据：

bq query --use_legacy_sql=false "UPDATE data_set.table_to_update A
SET D_A_AMOUNT = B.D_A_AMOUNT, A_FEE = B.A_FEE,
O_T_AMOUNT = B.O_T_AMOUNT, U_FEE = B.U_FEE,
UPDATED_DATETIME = B.UPDATED_DATETIME, UPDATED_BY = B.UPDATED_BY
FROM (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY TRANSACTION_KEY ORDER BY UPDATED_DATETIME DESC) AS rn
    FROM (
        SELECT CAST(131.27 AS NUMERIC) AS D_A_AMOUNT, CAST(20.66 AS NUMERIC) AS A_FEE,
        '12345' AS TRANSACTION_KEY, CAST(145871.0 AS NUMERIC) AS O_T_AMOUNT,
        CAST(131.27 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME,
        'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY
        UNION ALL  
        SELECT CAST(134.19 AS NUMERIC) AS D_A_AMOUNT, CAST(21.31 AS NUMERIC) AS A_FEE,
        '987654232' AS TRANSACTION_KEY, CAST(149112.0 AS NUMERIC) AS O_T_AMOUNT,
        CAST(134.19 AS NUMERIC) AS U_FEE, '2022-09-28 15:30:47' AS UPDATED_DATETIME,
        'APPLE_USER' AS UPDATED_BY, 'P_S_US' AS L_IDENTITY
    )
) B
ON A.TRANSACTION_KEY = B.TRANSACTION_KEY 
WHERE A.L_IDENTITY = 'P_S_US' AND B.rn = 1;"

注：ROW_NUMBER()会按UPDATED_DATETIME倒序排序，取每个TRANSACTION_KEY的最新行作为更新源，你可以根据实际需求调整排序规则。

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