Snowflake中如何用循环按internal_id分组更新row_id并解决报错

原代码存在的问题 #

• 报错原因：Snowflake的UPDATE语句无法直接引用存储过程内的局部变量counter，这就是你遇到"invalid identifier 'COUNTER'"错误的原因。
• 逻辑错误：循环内的UPDATE语句每次都会把目标internal_id下的所有行row_id设为当前counter值，最终该分组下所有行的row_id都会是同一个最大值，完全没实现分组内从1开始递增的需求。
• 扩展性差：只能处理单个internal_id，无法批量覆盖所有分组。

正确的批量处理方案 #

不需要使用存储过程，直接利用窗口函数ROW_NUMBER()结合MERGE语句就能高效完成所有分组的row_id递增赋值，代码如下：

MERGE INTO "DB_MX_DEV"."STAGING"."stg_chedraui_inv_day" tgt
USING (
    SELECT 
        internal_id,
        -- 替换成表的唯一主键字段（比如业务主键id）来精准匹配行，避免row_id初始值重复导致的匹配问题
        your_primary_key_column,
        -- ORDER BY指定分组内的排序逻辑，比如创建时间、业务字段，保证顺序稳定；无特定需求可改用(SELECT NULL)
        ROW_NUMBER() OVER (PARTITION BY internal_id ORDER BY your_sort_column) AS new_row_id
    FROM "DB_MX_DEV"."STAGING"."stg_chedraui_inv_day"
) src
ON tgt.internal_id = src.internal_id 
AND tgt.your_primary_key_column = src.your_primary_key_column
WHEN MATCHED THEN
    UPDATE SET tgt.row_id = src.new_row_id;

补充说明 #

• 如果表没有唯一主键，在row_id初始值唯一的前提下可以用它作为匹配字段，但更推荐使用表的唯一标识字段匹配，避免更新错误。
• ORDER BY子句决定分组内row_id的递增顺序，务必根据业务需求指定合适的排序字段，保证结果符合预期。
• 该方案为批量操作，相比循环更新效率提升极大，适合大数据量场景。

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