PostgreSQL使用CTE执行UPDATE报错：missing FROM-clause entry for table "cte"

错误根因 #

• 你的UPDATE语句没有显式将CTE作为关联数据源加入FROM子句，PostgreSQL不允许直接在UPDATE的SET、WHERE中引用未声明的数据源，因此触发了表不存在的错误。

修正后的写法 #

WITH cte AS(
    SELECT * FROM test_user WHERE user_id = NEW.user_id
)
UPDATE test_username 
SET money_counter = money_counter + 1, 
    total_revenue = total_revenue + cte.total_revenue
FROM cte
WHERE test_username.username = cte.username;

额外注意事项 #

• 如果test_user表中user_id不是唯一键，CTE可能返回多条匹配记录，会导致同一条test_username记录被重复更新，建议根据业务场景提前对CTE结果做去重处理，比如用DISTINCT或者LIMIT 1限定返回结果。

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