PostgreSQL 15：如何更新表时为每行动态选取未存在于该表的值

你的问题核心在于PostgreSQL的语句级快照隔离：执行UPDATE时，所有子查询引用的表数据都是语句开始时的快照，不会随着更新过程实时变化，所以你的原语句会把所有行更新为同一个值。

要实现每行分配唯一的、表a中未在表b出现过的id，可以通过给两行集分别排序并建立一一对应关系来完成，具体SQL如下：

-- 生成带序号的b行和可用的a行，然后匹配更新
WITH ranked_b AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM b
),
ranked_a AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM a
    WHERE id NOT IN (SELECT a FROM b) -- 筛选b中尚未使用的a的id
)
UPDATE b
SET a = ranked_a.id
FROM ranked_b
JOIN ranked_a ON ranked_b.rn = ranked_a.rn
WHERE b.id = ranked_b.id;

执行后查询表b：

SELECT * FROM b;

得到的结果符合预期：

id | a 
----+---
  1 | 1
  2 | 2
  3 | 3

原理说明 #

1. ranked_b给表b的每一行按id排序分配序号，确保每行有唯一标识；
2. ranked_a筛选出表a中未被表b使用的id，同样按id排序分配序号；
3. 通过序号将两行集关联，实现每行b对应唯一的可用a.id，完成批量更新。

如果表b的行数多于表a中可用的id数量，多余的行不会被更新（可根据需求调整逻辑，比如设置默认值或重复分配）。

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