Postgres9.6+PGAdmin中基于关联SELECT实现多表UPDATE的方案问询

你可以通过PL/pgSQL的DO语句实现单脚本统一执行，以下是两种可直接运行的实现方案：

方式1：独立UPDATE拼接（最稳妥，无动态SQL语法风险） #

直接复用你已经验证过的单表更新逻辑，补充表对应的plan.class筛选条件即可，完全规避ID重复问题：

DO $$
BEGIN
  -- 更新table1
  UPDATE table1 
  SET number = 44
  WHERE table1.id IN
    (SELECT table1.id FROM plan
     JOIN name ON name.id = plan.nameid
     JOIN type ON type.id = name.typeid
     JOIN simul ON simul.id = plan.simulid
     LEFT JOIN table1 ON table1.id = tableid
     WHERE type.tag = 'X' 
       AND plan.class LIKE '%Table1%' -- 匹配table1对应的class规则
       AND plan.class LIKE '%Table%' 
       AND simul.label IN ('SIMUL90','SIMUL99','SIMUL87'));

  -- 更新table2
  UPDATE table2 
  SET number = 44
  WHERE table2.id IN
    (SELECT table2.id FROM plan
     JOIN name ON name.id = plan.nameid
     JOIN type ON type.id = name.typeid
     JOIN simul ON simul.id = plan.simulid
     LEFT JOIN table2 ON table2.id = tableid
     WHERE type.tag = 'X' 
       AND plan.class LIKE '%Table2%' -- 匹配table2对应的class规则
       AND plan.class LIKE '%Table%' 
       AND simul.label IN ('SIMUL90','SIMUL99','SIMUL87'));
END $$ LANGUAGE plpgsql;

方式2：循环动态执行（适合后续需要扩展更多同结构表的场景） #

你之前LOOP执行失败大概率是动态SQL拼接的语法问题，以下是修复后的循环版本：

DO $$
DECLARE
  table_list text[] := ARRAY['table1', 'table2']; -- 可扩展更多表名
  target_table text;
BEGIN
  FOREACH target_table IN ARRAY table_list
  LOOP
    EXECUTE format(
      'UPDATE %I 
       SET number = 44
       WHERE %I.id IN
         (SELECT %I.id FROM plan
          JOIN name ON name.id = plan.nameid
          JOIN type ON type.id = name.typeid
          JOIN simul ON simul.id = plan.simulid
          LEFT JOIN %I ON %I.id = tableid
          WHERE type.tag = ''X'' 
            AND plan.class LIKE ''%'||target_table||'%''
            AND plan.class LIKE ''%Table%'' 
            AND simul.label IN (''SIMUL90'',''SIMUL99'',''SIMUL87''))',
      target_table, target_table, target_table, target_table, target_table
    );
  END LOOP;
END $$ LANGUAGE plpgsql;

%I是format函数的标识符占位符，会自动转义表名避免语法错误和注入风险，字符串中的单引号需要转义为两个单引号，这是动态SQL最常见的写法坑。

注意事项 #

• 执行前建议先把UPDATE语句替换为SELECT查询，确认待更新的行数和ID符合预期后再执行更新
• 两种方案都完全兼容PostgreSQL 9.6版本，可直接在PGAdmin III中运行整个脚本

内容的提问来源于stack exchange，提问作者Stuart K. Smith