MERGE语句更新struct数组并保持排序时报相关子查询不支持如何解决

Correlated Subquery is unsupported in UPDATE clause是BigQuery对MERGE语句UPDATE子句的语法限制：

• 你使用的ARRAY(SELECT AS STRUCT * FROM UNNEST(ARRAY_CONCAT(target.arr, source.arr)) ORDER BY timestamp ASC)属于关联子查询，子查询内部引用了当前匹配行的target.arr和source.arr两个字段
• 当前BigQuery不支持在MERGE的UPDATE赋值表达式中使用这类关联到当前行的子查询，因此触发报错。你最初遇到的Unexpected keyword SELECT是旧版SQL解析器对嵌套子查询写法的识别错误，本质原因还是子查询的使用位置不符合语法要求。

另外注意你代码中排序字段写的是timestamp，但示例结构体中的排序字段是t，需要修正避免字段不存在的报错。

提供两种可落地的写法：

方案1：USING阶段预计算排序后数组（推荐，性能最优） #

将数组拼接、排序的逻辑提前到源表预处理阶段，UPDATE时直接赋值即可，无需在UPDATE子句中写子查询。完整MERGE示例如下：

MERGE target_table AS target
USING (
  SELECT
    s.id AS join_key,
    -- 合并目标表原有数组和源表新数组后按t升序排序
    ARRAY_AGG(item ORDER BY item.t ASC) AS sorted_arr
  FROM source_table AS s
  -- 提前关联目标表获取原有数组
  JOIN target_table AS t ON s.id = t.id,
  UNNEST(ARRAY_CONCAT(t.arr, s.arr)) AS item
  GROUP BY s.id
) AS source
ON target.id = source.join_key
WHEN MATCHED THEN
  UPDATE SET arr = source.sorted_arr
-- 按需补充未匹配时的插入逻辑
WHEN NOT MATCHED THEN
  INSERT (id, arr) VALUES (source.join_key, source.sorted_arr)

方案2：拆分操作为拼接+两步更新 #

如果不想修改原有MERGE的关联逻辑，可以拆分操作实现需求：

1. 先执行MERGE仅完成数组拼接

MERGE target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET arr = ARRAY_CONCAT(target.arr, source.arr)
WHEN NOT MATCHED THEN
  INSERT (id, arr) VALUES (source.id, source.arr)

2. 再执行单独的UPDATE完成数组排序，该场景下关联子查询是语法允许的：

UPDATE target_table
SET arr = ARRAY(SELECT AS STRUCT * FROM UNNEST(arr) ORDER BY t ASC)
-- 可加过滤条件只更新刚修改的行，大幅提升性能
WHERE id IN (SELECT id FROM source_table)

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