如何在PostgreSQL中批量执行针对多ID的多条UPDATE语句？

问题背景 #

我有3条UPDATE语句，用于交换同一ID下fid=3771和fid=3782的val值，现在需要针对一批ID执行这些操作，不想逐个ID手动运行语句。

单ID执行的语句如下：

-- 存储ContractDate值到临时列
update tval set temp_col = (select val from tval where id = 402280209 and fid = 3782) where id = 402280209 and fid = 3782

-- 用maturityDate替换ContractDate
update tval set val= (select val from tval where fid = 3771 and id = 402280209) where fid = 3782 and id = 402280209

-- 将MaturityDate设置为ContractDate的值
update tval set val = (select temp_col from tval where id = 402280209 and fid = 3782) where id = 402280209 and fid = 3771

表结构示例：

id       fid              ts                      val                 temp_col
402280209   3765    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000   
402280209   3771    2021-09-20 00:00:00.000   2023-09-20 00:00:00.000   <---- 需交换的列
402280209   3782    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000   <----- 需交换的列

期望结果：

id       fid              ts                      val                 temp_col
402280209   3765    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000   
402280209   3771    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000   
402280209   3782    2021-09-20 00:00:00.000   2023-09-20 00:00:00.000   

批量实现方案 #

方案1：高效单步更新（无需临时列） #

通过自连接和CTE定义批量ID列表，一次更新完成交换：

WITH id_list AS (
    -- 在这里添加所有需要处理的ID
    SELECT 402280209 AS id
    UNION ALL SELECT 123456789
    UNION ALL SELECT 987654321
    -- 可继续追加更多ID
)
UPDATE tval t1
JOIN tval t2 ON t1.id = t2.id 
JOIN id_list il ON t1.id = il.id
SET 
    t1.val = CASE 
                WHEN t1.fid = 3771 THEN t2.val  -- 3771取3782的val
                WHEN t1.fid = 3782 THEN t2.val  -- 3782取3771的val
                ELSE t1.val  -- 其他fid保持不变
             END
WHERE 
    -- 只处理需要交换的两个fid组合
    (t1.fid = 3771 AND t2.fid = 3782) 
    OR (t1.fid = 3782 AND t2.fid = 3771);

方案2：兼容原有临时列逻辑的批量操作 #

如果需要保留临时列的使用逻辑，可以分三步批量执行：

WITH id_list AS (
    SELECT 402280209 AS id
    UNION ALL SELECT 123456789
    -- 添加更多ID
)
-- 1. 批量将3782的val存入临时列
UPDATE tval 
SET temp_col = val 
WHERE fid = 3782 AND id IN (SELECT id FROM id_list);

-- 2. 批量用3771的val替换3782的val
UPDATE tval t1
JOIN tval t2 ON t1.id = t2.id
JOIN id_list il ON t1.id = il.id
SET t1.val = t2.val
WHERE t1.fid = 3782 AND t2.fid = 3771;

-- 3. 批量用临时列的值更新3771的val
UPDATE tval t1
JOIN tval t2 ON t1.id = t2.id
JOIN id_list il ON t1.id = il.id
SET t1.val = t2.temp_col
WHERE t1.fid = 3771 AND t2.fid = 3782;

验证建议 #

执行更新前，建议先通过SELECT语句确认结果是否符合预期：

WITH id_list AS (SELECT 402280209 AS id)
SELECT 
    t1.id,
    t1.fid,
    t1.ts,
    CASE 
        WHEN t1.fid = 3771 THEN t2.val 
        WHEN t1.fid = 3782 THEN t2.val 
        ELSE t1.val 
    END AS expected_val,
    t1.val AS original_val
FROM tval t1
JOIN tval t2 ON t1.id = t2.id
JOIN id_list il ON t1.id = il.id
WHERE t1.fid IN (3771, 3782);

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