PostgreSQL：UPDATE语句中如何关联未在FROM子句引用的表？

这个问题我之前碰到过，PostgreSQL对UPDATE语句里目标表的引用范围有明确限制，咱们先搞清楚错误原因，再来看怎么调整语句。

错误原因 #

你原来的写法里，在FROM子句的显式JOIN（openb op JOIN b_b bb ON bb.id = ba.bb_id）里引用了目标表ba，但PostgreSQL不允许在FROM子句的JOIN条件中直接引用UPDATE的目标表——因为FROM子句里的表是作为独立数据集处理的，和目标表的作用域不重叠，所以会抛出invalid reference to FROM-clause entry for table "ba"的错误。

解决方案 #

下面给你几种可行的调整方式，按需选择：

方法1：用EXISTS子句筛选符合条件的记录 #

这种方式适合大数据量场景，性能通常更优，因为只要找到匹配的记录就会停止查询：

UPDATE b_address ba
SET op_id = op.land_id
FROM openb op
WHERE EXISTS (
    SELECT 1 
    FROM b_b bb 
    WHERE bb.id = ba.bb_id AND bb.source = 'E'
  )
  AND ba.str = op.name
  AND (ba.openb_id IS NULL OR ba.openb_id = '');

方法2：用IN子句筛选目标表的关联ID #

如果b_b.id是唯一值，用IN子句写法更直观：

UPDATE b_address ba
SET op_id = op.land_id
FROM openb op
WHERE ba.bb_id IN (SELECT bb.id FROM b_b bb WHERE bb.source = 'E')
  AND ba.str = op.name
  AND (ba.openb_id IS NULL OR ba.openb_id = '');

方法3：用隐式JOIN（逗号分隔表）替代显式JOIN #

把b_b放到FROM子句里，用逗号分隔，然后在WHERE里写关联条件，这样就不会触发作用域问题：

UPDATE b_address ba
SET op_id = op.land_id
FROM openb op, b_b bb
WHERE bb.id = ba.bb_id
  AND bb.source = 'E'
  AND ba.str = op.name
  AND (ba.openb_id IS NULL OR ba.openb_id = '');

这三种写法都能实现你的需求，你可以根据自己表的数据量和索引情况选择最适合的一种。

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