如何在Redshift中循环0-9实现分批UPDATE操作？

需求说明 #

现有Redshift环境下的UPDATE语句如下：

UPDATE users
SET birthday = temp_users.birth_date
FROM public.users as gpu 
INNER JOIN temp_schema.users_birth_dates_with_row_numbers AS temp_users ON gpu.id = temp_users.id
WHERE mod(temp_users.row_id, 10) = 0 -- 需将此处值从0改为9循环执行
;

需要循环执行该语句10次，每次将mod(temp_users.row_id, 10) = X中的X从0依次改为9，通过分批更新避免长时间持有锁。

实现方法 #

Redshift支持PL/pgSQL语法，可通过创建存储过程实现循环逻辑：

1. 创建存储过程 #

CREATE OR REPLACE PROCEDURE batch_update_users_birthday()
LANGUAGE plpgsql
AS $$
DECLARE
    loop_counter INT := 0; -- 初始化循环计数器
BEGIN
    -- 循环10次，计数器覆盖0到9
    WHILE loop_counter < 10 LOOP
        EXECUTE format('
            UPDATE users
            SET birthday = temp_users.birth_date
            FROM public.users as gpu 
            INNER JOIN temp_schema.users_birth_dates_with_row_numbers AS temp_users ON gpu.id = temp_users.id
            WHERE mod(temp_users.row_id, 10) = %s
        ', loop_counter);
        
        -- 提交当前批次更新，及时释放锁
        COMMIT;
        
        -- 计数器自增
        loop_counter := loop_counter + 1;
    END LOOP;
END;
$$;

2. 执行存储过程 #

CALL batch_update_users_birthday();

3. 可选：删除存储过程（若不再需要） #

DROP PROCEDURE IF EXISTS batch_update_users_birthday();

关键说明 #

• 用EXECUTE format()动态拼接SQL，实现每次循环修改mod的匹配值
• 每批次更新后执行COMMIT，避免长时间持有锁占用资源
• 循环计数器从0到9，刚好满足10次分批执行的需求

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