PostgreSQL如何使用UPDATE FROM关联CTE更新同ID匹配的class字段

你定义了两个返回id、class字段的CTE，初始代码（注意原代码里x定义结尾多了个多余的反引号，属于笔误）如下：

with x as (select id, class from table1 where paramenter='D'), 
    y as (select id, class from table1 where paramenter='F')

需求为：将CTE y覆盖的所有记录的class字段，更新为CTE x中相同id对应的class值。之前编写的错误UPDATE语句如下，执行效果不符合预期：

update table1 set class=x.class from x where x.id=y.id

• 语句没有引入CTE y，也没有限定更新范围是paramenter='F'的记录，更新边界完全错误，存在误改全表数据的风险。
• 关联逻辑缺失，数据库无法识别待更新行和两个CTE的匹配关系。

标准CTE更新写法（兼容PostgreSQL、SQL Server、MySQL 8.0+等支持CTE的数据库） #

WITH x AS (
    SELECT id, class 
    FROM table1 
    WHERE paramenter = 'D'
),
y AS (
    SELECT id, class 
    FROM table1 
    WHERE paramenter = 'F'
)
UPDATE table1 t
SET class = x.class
FROM y
INNER JOIN x ON x.id = y.id
WHERE t.id = y.id;

逻辑说明 #

• 给待更新的原表起别名t，通过t.id = y.id明确限定只更新CTE y覆盖的paramenter='F'的行，不会误改其他数据。
• 通过x.id = y.id匹配两个CTE中id一致的记录，将x对应的class值赋值给待更新行。

注意：如果CTE x中存在同一个id对应多条不同class记录的情况，更新会触发报错，或随机取一条值赋值。使用前请确认x中id唯一，存在重复时可通过去重、分组聚合等方式提前处理。

简化写法（无需显式定义y CTE） #

如果不需要在语句其他位置复用y的逻辑，可以直接在WHERE条件中限定更新范围，代码更简洁：

WITH x AS (
    SELECT id, class 
    FROM table1 
    WHERE paramenter = 'D'
)
UPDATE table1 t
SET class = x.class
FROM x
WHERE t.paramenter = 'F' 
  AND x.id = t.id;

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