如何使用含窗口函数（stuff、dense_rank、over）的SELECT语句更新列值

正确实现方案 #

你原写法存在两个问题：

• SET 子句后直接跟未包裹的SELECT语句，不符合SQL语法规范
• 子查询没有和外层要更新的行做唯一关联，执行时会触发多行返回报错，或导致所有行被赋错误值

推荐写法（适用于SQL Server） #

用可更新CTE实现，逻辑直观无需额外关联：

WITH UpdateCTE AS (
    SELECT 
        ColumnA,
        -- 按你的逻辑提前计算好新值
        NewColumnA = STUFF(ColumnA, 1, 2, 11 + DENSE_RANK() OVER(ORDER BY LEFT(ColumnA, 10)))
    FROM TableA
    WHERE Year = '2021'
      AND Type = 'LA'
)
-- 直接更新CTE会自动映射到原表对应行
UPDATE UpdateCTE
SET ColumnA = NewColumnA;

通用兼容写法（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

通过表主键关联更新，需要替换代码中的主键ID为你表的实际主键字段：

-- MySQL 写法示例
UPDATE TableA a
INNER JOIN (
    SELECT 
        主键ID,
        STUFF(ColumnA, 1, 2, 11 + DENSE_RANK() OVER(ORDER BY LEFT(ColumnA, 10))) AS NewColumnA
    FROM TableA
    WHERE Year = '2021'
      AND Type = 'LA'
) b ON a.主键ID = b.主键ID
SET a.ColumnA = b.NewColumnA;

注意事项 #

• 执行更新前先单独运行CTE或子查询里的SELECT逻辑，确认计算出的新值符合业务预期，避免误改数据
• 如果ColumnA为数值类型，需要对STUFF返回的字符串结果做类型转换再赋值
• 注意11 + DENSE_RANK()的结果长度，如果排名过高导致结果超过2位，替换后ColumnA的长度会发生变化，需确认符合你的设计要求

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