PostgreSQL中如何基于临时表批量修改指定列值？

需求说明 #

• 目标表 commissions 包含字段：id（主键）、ticketnumber（票号）、processed（布尔类型，需将指定记录的值从t改为f）
• 临时表 tickettemp 存储了需要修改的所有ticketnumber
• 要求：不使用表连接，通过循环临时表的方式匹配修改commissions表的processed字段

示例表结构及数据 #

-- 创建并填充commissions表
CREATE TABLE commissions (
    id INT PRIMARY KEY,
    ticketnumber VARCHAR(50) NOT NULL,
    processed BOOLEAN NOT NULL DEFAULT TRUE
);

INSERT INTO commissions VALUES
(1, 'TICKET001', TRUE),
(2, 'TICKET002', TRUE),
(3, 'TICKET003', TRUE);

-- 创建并填充临时表tickettemp
CREATE TEMP TABLE tickettemp (
    ticketnumber VARCHAR(50) NOT NULL
);

INSERT INTO tickettemp VALUES
('TICKET001'),
('TICKET003');

循环实现的PL/pgSQL脚本 #

可以通过PostgreSQL的PL/pgSQL编写匿名块实现循环更新：

DO $$
DECLARE
    rec_ticket RECORD;
BEGIN
    -- 遍历tickettemp中的每一条票号记录
    FOR rec_ticket IN SELECT ticketnumber FROM tickettemp LOOP
        -- 更新commissions中匹配的记录
        UPDATE commissions
        SET processed = FALSE
        WHERE ticketnumber = rec_ticket.ticketnumber;
    END LOOP;
END $$;

注意事项 #

• 这种循环方式确实可行，但效率远低于批量更新：9000条记录会执行9000次独立的UPDATE操作，而用表连接的批量更新（如下）只需要一次操作，性能差异明显：

  UPDATE commissions c
  SET processed = FALSE
  WHERE EXISTS (
      SELECT 1 FROM tickettemp t 
      WHERE t.ticketnumber = c.ticketnumber
  );
  

• 如果没有强制限制，优先推荐使用批量更新的方式；若必须用循环，上述脚本可满足需求。

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