如何在BigQuery中从子查询表向目标表数组列插入数据及更优方案

你的当前写法在Table2中对应同一个column1有多行时会报错（子查询返回多个结果），下面提供几种更健壮、更高效的实现方式：

方式1：用ARRAY()子查询兼容多行场景 #

如果Table2中可能存在多个相同column1的行，这种方式可以一次性把所有对应值合并到数组，同时兼容单行情况：

UPDATE `project.dataset.Table1` t1
SET 
  column3 = ARRAY_CONCAT(t1.column3, ARRAY(SELECT t2.column3 FROM `project.dataset.Table2` t2 WHERE t2.column1 = t1.column1)),
  column4 = ARRAY_CONCAT(t1.column4, ARRAY(SELECT t2.column4 FROM `project.dataset.Table2` t2 WHERE t2.column1 = t1.column1))
WHERE EXISTS (SELECT 1 FROM `project.dataset.Table2` t2 WHERE t2.column1 = t1.column1)

• 优势：无需硬编码具体column1值，批量更新所有匹配行，自动处理Table2同column1的多行数据。

方式2：用MERGE语句实现灵活批量操作 #

MERGE支持匹配时更新、不匹配时插入，适合统一处理合并逻辑的场景：

MERGE `project.dataset.Table1` t1
USING (
  SELECT 
    column1,
    ARRAY_AGG(column3) AS new_column3,
    ARRAY_AGG(column4) AS new_column4
  FROM `project.dataset.Table2`
  GROUP BY column1
) t2
ON t1.column1 = t2.column1
WHEN MATCHED THEN
  UPDATE SET
    column3 = ARRAY_CONCAT(t1.column3, t2.new_column3),
    column4 = ARRAY_CONCAT(t1.column4, t2.new_column4)

• 优势：先对Table2按column1聚合，减少关联次数；可扩展插入逻辑（若需处理Table1中不存在的column1行，可添加WHEN NOT MATCHED THEN INSERT分支）。

方式3：单行场景下的简化写法 #

如果确定Table2中对应column1只有一行，可直接关联两表简化语法：

UPDATE `project.dataset.Table1` t1
SET 
  column3 = ARRAY_CONCAT(t1.column3, [t2.column3]),
  column4 = ARRAY_CONCAT(t1.column4, [t2.column4])
FROM `project.dataset.Table2` t2
WHERE t1.column1 = t2.column1

• 优势：语法简洁，避免子查询嵌套，执行效率更高。

额外提示 #

如果需要对合并后的数组去重，可在ARRAY_CONCAT后结合ARRAY(SELECT DISTINCT x FROM UNNEST(...) x)处理。

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