在大查询中更新表时合并子查询中的所有项

Question

我在Big Query中有一个以下查询。我正在尝试使用此查询update一个表。我从CLI调用sql命令。

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';"

我得到低于错误

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

我是Big Query的新手。我不知道这里的问题是什么。

我如何使我的第二个查询工作良好

Answer 1

您只需要使用UNION All语句创建一个临时表，然后像下面的一样连接

bq query --use_legacy_sql=false "CREATE OR REPLACE TABLE data_set.update_union AS (
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); 
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 data_set.update_union B 
ON C.TRANSACTION_KEY = B.TRANSACTION_KEY  
WHERE C.L_IDENTITY = 'P_S_US' AND 1 > 0 and C.TRANSACTION_KEY = A.TRANSACTION_KEY;"