如何在UPDATE语句中结合PARTITION BY更新表的RECENT字段？

现有查询语句：

SELECT *, ORDER = MAX(ORDER) OVER (PARTITION BY ID)
FROM MY_TABLE

当前MY_TABLE的数据：

期望更新后的结果：

尝试过以下UPDATE语句，但不确定如何在UPDATE中正确结合PARTITION BY：

UPDATE MY_TABLE
SET RECENT = FALSE
WHERE ORDER <> MAX(ORDER) OVER (PARTITION BY ID);

可以通过子查询或CTE（公共表表达式）间接结合分组逻辑实现需求，窗口函数无法直接用于UPDATE的WHERE子句。以下是几种通用实现方式：

方式一：CTE分组计算最大值后关联更新 #

WITH MaxOrders AS (
    SELECT ID, MAX(ORDER) AS MaxOrder
    FROM MY_TABLE
    GROUP BY ID
)
UPDATE MY_TABLE t
SET RECENT = CASE WHEN t.ORDER = mo.MaxOrder THEN TRUE ELSE FALSE END
FROM MaxOrders mo
WHERE t.ID = mo.ID;

方式二：子查询关联更新 #

UPDATE MY_TABLE t
SET RECENT = (
    SELECT CASE WHEN t.ORDER = MAX(ORDER) THEN TRUE ELSE FALSE END
    FROM MY_TABLE
    WHERE ID = t.ID
);

特殊数据库适配（Oracle） #

Oracle可使用MERGE语句实现：

MERGE INTO MY_TABLE t
USING (
    SELECT ID, MAX(ORDER) AS MaxOrder
    FROM MY_TABLE
    GROUP BY ID
) mo ON (t.ID = mo.ID)
WHEN MATCHED THEN
    UPDATE SET RECENT = CASE WHEN t.ORDER = mo.MaxOrder THEN 'TRUE' ELSE 'FALSE' END;

注意 #

ORDER是多数数据库的保留字，建议用反引号（MySQL：`ORDER`）或方括号（SQL Server：[ORDER]）包裹，避免语法冲突。

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