UPDATE语句OUTPUT子句返回行号错误问题咨询

原因分析 #

问题的核心是：CTE中的rn是动态计算的虚拟列，并非基表#table的物理列。当执行UPDATE并使用OUTPUT子句时，deleted和inserted指向的是基表的原始行数据，而非CTE的计算结果。当你在OUTPUT中引用deleted.rn时，SQL Server会针对deleted里的单一行（原i=2的记录）重新执行ROW_NUMBER()计算——由于只有一行数据，计算出的rn必然是1，而非CTE初始计算的2。

解决方法 #

要获取正确的rn，必须把rn作为物理列存储或预先锁定其值，避免动态计算的干扰，以下是两种可行方案：

方案1：将带rn的数据持久化到临时表后更新 #

select * into #table from (values (1),(2)) t(i)

-- 先把rn作为物理列存入临时表
select *, ROW_NUMBER() over (order by i) rn into #temp_table from #table

update #temp_table
set i = i * 10
output deleted.i, deleted.rn, inserted.i
where rn = 2

此时rn是临时表的实际列，OUTPUT会直接返回存储的原始值，结果中的rn即为预期的2。

方案2：通过子查询关联基表与预先计算的rn #

如果不想创建额外临时表，可通过子查询预先计算rn并与基表关联，确保OUTPUT能引用到正确的数值：

select * into #table from (values (1),(2)) t(i)

update t
set i = i * 10
output deleted.i, s.rn, inserted.i
from #table t
inner join (
    select i, ROW_NUMBER() over (order by i) rn
    from #table
) s on t.i = s.i
where s.rn = 2

这里子查询预先计算出所有行的rn，更新时通过关联锁定目标行，OUTPUT直接引用子查询中的rn，就能得到正确结果。

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