Snowflake中UPDATE语句关联子查询执行报错问题

报错产生原因 #

• Snowflake对UPDATE语句中的关联子查询支持有严格限制，你使用的逐行匹配关联子查询属于Snowflake当前不支持的类型：和MySQL的优化逻辑不同，MySQL允许这类子查询降级执行，而Snowflake为了保障分布式计算性能，直接拦截了这类执行效率较低的逐行遍历子查询写法。
• 如果PROVIDER_TABLE中存在同一个XPI对应多条PROVIDER_ID记录的情况，该子查询会返回多行结果：MySQL会隐式取第一条匹配值执行更新，而Snowflake不会做这类隐式处理，也会触发该报错。

对应解决方法 #

方案1：使用UPDATE JOIN语法改写（推荐） #

Snowflake原生支持UPDATE时直接关联匹配表，执行效率更高，写法如下：

UPDATE PROVIDER_XO_SCORE_TABLE PXS
SET PXS.PROVIDER_ID = P.PROVIDER_ID
FROM PROVIDER_TABLE P
WHERE PXS.XPI = P.XPI;

如果PROVIDER_TABLE存在同一个XPI对应多个PROVIDER_ID的情况，需要先对关联表做去重处理，比如固定取最大的PROVIDER_ID：

UPDATE PROVIDER_XO_SCORE_TABLE PXS
SET PXS.PROVIDER_ID = P.PROVIDER_ID
FROM (
    SELECT XPI, MAX(PROVIDER_ID) AS PROVIDER_ID
    FROM PROVIDER_TABLE
    GROUP BY XPI
) P
WHERE PXS.XPI = P.XPI;

方案2：使用MERGE语句改写 #

如果后续需要扩展不匹配时的插入逻辑，可以用兼容性更强的MERGE语法：

MERGE INTO PROVIDER_XO_SCORE_TABLE PXS
USING PROVIDER_TABLE P
ON PXS.XPI = P.XPI
WHEN MATCHED THEN UPDATE SET PXS.PROVIDER_ID = P.PROVIDER_ID;

存在重复XPI的场景下，同样需要先对USING子句中的表做去重处理。

内容的提问来源于stack exchange，提问作者Shailesh Vikram Singh