PostgreSQL批量更新报错：子查询返回多行，如何用CTE实现？

当然可以用单条CTE语句完成这个更新，你原来的写法报错是因为VALUES子句里的子查询返回了多行结果，而VALUES的每个元素只能接收单个值。下面提供两种可行的方案：

方案1：用窗口函数（推荐，性能更优） #

通过row_number()窗口函数给符合条件的行排序并分配序号，直接根据序号判断要设置的状态：

WITH ranked_people AS (
    SELECT 
        id,
        CASE 
            WHEN row_number() OVER (ORDER BY date_registered DESC) <= 5 THEN 'X'
            ELSE 'Y'
        END AS new_status
    FROM people
    WHERE surname = 'foo'
)
UPDATE people p
SET status = rp.new_status
FROM ranked_people rp
WHERE p.id = rp.id;

这个方案只需要扫描一次people表，比两次扫描的写法效率更高，逻辑也更简洁。

方案2：用UNION ALL合并两组数据 #

如果你更倾向于保留原思路的分组逻辑，可以把两个CTE的结果用UNION ALL合并成带状态标识的数据集，再关联更新：

WITH status_assignments AS (
    SELECT id, 'X' AS new_status
    FROM people
    WHERE surname = 'foo'
    ORDER BY date_registered DESC
    LIMIT 5
    UNION ALL
    SELECT id, 'Y' AS new_status
    FROM people
    WHERE surname = 'foo'
    ORDER BY date_registered DESC
    OFFSET 5
)
UPDATE people p
SET status = sa.new_status
FROM status_assignments sa
WHERE p.id = sa.id;

注意这种写法会扫描两次people表，当数据量较大时性能不如窗口函数方案。

错误原因说明 #

你原来的VALUES子句中，(SELECT id from x_status)会返回5个ID（多行），但VALUES的每一行只能接受单个值，PostgreSQL无法将多行结果作为单个元素处理，因此抛出more than one row returned by a subquery used as an expression错误。

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