如何在Snowflake中使用WITH CTE（公共表表达式）执行UPDATE更新语句

原代码存在的问题 #

你当前编写的SQL不符合Snowflake的UPDATE语法规范，存在两个明显错误：

• CTE嵌套在WHERE子句中，Snowflake要求DML语句中的CTE必须定义在整个语句的最开头
• 已经在UPDATE后指定了目标表dw.tb_fidctp_order，又在FROM子句重复声明该表，会导致不必要的自关联，可能出现结果不符合预期甚至执行报错的问题

正确实现代码 #

WITH my_cte AS (
    SELECT  
        order_id, 
        MAX(CAST(VERSION AS NUMBER(18,0))) AS max_version
    FROM stg.tb_fidctp_order_input 
    GROUP BY order_id
)
UPDATE dw.tb_fidctp_order t
SET last_version_flag = 'N'
FROM my_cte c
WHERE t.order_id = c.order_id 
  AND t.version < c.max_version;

逻辑说明 #

代码先通过CTE预计算每个订单ID对应的最大版本号，再将目标更新表和CTE做关联，所有匹配到的、版本号小于同订单最大版本号的记录，都会将last_version_flag字段更新为N，完全符合你的业务需求。

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