SQL执行UPDATE用ROW_NUMBER() OVER报错窗口函数位置非法如何解决

报错原因 #

窗口函数（如ROW_NUMBER()）不支持直接写在UPDATE语句的SET子句中，SQL语法要求窗口函数只能出现在SELECT或ORDER BY子句里，这是触发报错的核心原因。

修改思路 #

1. 提前用CTE（公共表表达式）完成所有计算逻辑：包括关联分组数据、按[BREAG_BGL_ID]分组生成独立序列、预查询每个分组当年的最大序列号
2. 直接关联预计算结果执行UPDATE，SET子句直接读取预计算好的数值，规避在SET中使用窗口函数的问题

完整修改后代码 #

WITH UpdatePreCalc AS (
    SELECT
        -- 保留原表字段用于后续关联更新
        G1.*,
        G2.GROUP_ID,
        -- 按分组独立生成序列，满足每个BREAG_BGL_ID对应独立序列的需求
        ROW_NUMBER() OVER(PARTITION BY G2.GROUP_ID ORDER BY G1.[BREAG_BRE_ID] DESC) AS GROUP_RN,
        -- 预计算当前分组当年的最大序列号
        ISNULL((
            SELECT MAX([BREAG_GUEST_SEQ]) 
            FROM [BOS_RESADDGUEST]
            WHERE DATEPART(YEAR, [BREAG_DATEFROM]) = DATEPART(YEAR, GETDATE())
              AND [BREAG_BGL_ID] = G2.GROUP_ID
        ), 0) AS GROUP_MAX_SEQ
    FROM [BOS_RESADDGUEST] G1
    INNER JOIN (
        SELECT
             [BRE_ID]
            ,MAX([BRE_DATEFROM]) AS DATEFROM
            ,MAX([BRE_DATETO]) AS DATETO
            ,MAX([BGL_ID]) AS GROUP_ID
        FROM [BOS_RESERVATION]
        LEFT JOIN [BOS_UNIT_LIST] ON [BUL_ID] = [BRE_UNIT_ID]
        LEFT JOIN [BOS_UNITTYPE_LIST] ON [BUL_UNITTYPE_ID] = [BUT_ID]
        LEFT JOIN [BOS_GROUP_LIST] ON [BGL_ID] = [BUT_GROUP_ID]
        WHERE [BRE_ID] = @DIALOG_BRE_ID
        GROUP BY [BRE_ID]
    ) G2 ON G2.[BRE_ID] = G1.[BREAG_BRE_ID]
)
UPDATE G1
SET
    G1.[BREAG_BGL_ID] = CASE WHEN upd.[BREAG_BGL_ID] <> upd.GROUP_ID THEN upd.GROUP_ID ELSE upd.[BREAG_BGL_ID] END,
    G1.[BREAG_GUEST_SEQ] = CASE WHEN upd.[BREAG_BGL_ID] <> upd.GROUP_ID THEN upd.GROUP_RN + upd.GROUP_MAX_SEQ ELSE upd.[BREAG_GUEST_SEQ] END
FROM [BOS_RESADDGUEST] G1
-- 建议替换为表主键关联，避免重复数据导致更新错误
INNER JOIN UpdatePreCalc upd ON G1.[BREAG_BRE_ID] = upd.[BREAG_BRE_ID] AND G1.[BREAG_GUEST_SEQ] = upd.[BREAG_GUEST_SEQ]

注意事项 #

如果BOS_RESADDGUEST表有主键字段，将UPDATE部分的关联条件替换为主键匹配，更新效率和准确性会更高。

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