I have the following simple example to show this issue:
select * into #table from (values (1),(2)) t(i)
;with t as (
select *, ROW_NUMBER() over (order by i) rn
from #table
)
update t
set i = i * 10
output deleted.i, deleted.rn, inserted.i
where rn = 2
As expected, the UPDATE correctly updated 2 to 20. However, the OUTPUT shows
i | rn | i
---+----+---
2 | 1 | 20
rn is showing 1 instead of 2. Is there an explanation to this? And is there a solution to output the correct rn in the output, so that I can insert the correct value to a table to keep the change results.
Thank you.
I suspect that this is more a quirk than a bug, and more likely, you shouldn't be able to return the value of rn in the OUTPUT as it's not really part of the table that's being UPDATEd. Jonathan Willcock and siggemannen have done the hard work here on what is happening in the query plan, and I quote their comments respectively:
The optimiser has shifted the WHERE condition into the CTE. This is a reasonable thing to do, since the sooner the data is reduced, the faster it gets executed. I am not quite sure, however, why in the real world you want to do this: it would be much more practical for example to export the primary key, rather than a (possibly changeable) row number.
From what i could see in the query plan, after the update a new Project Sequence is initiated, which recalculates the ROW_NUMBER based on the updated data, which then returns rn = 1
I suspect to get the results you want, you should be using the ability of MERGE to return columns not from the target table, but also the source. This results in a more confusing query, I admit, but does give you the correct results:
SELECT *
INTO #table
FROM (VALUES (1),
(2)) t (i);
MERGE INTO #table WITH (HOLDLOCK) AS t
USING (SELECT *
FROM (SELECT t.i, t.i *10 AS i10, ROW_NUMBER() OVER (ORDER BY t.i) AS rn
FROM #table t)dt
WHERE dt.rn = 2) s ON t.i = s.i
WHEN MATCHED THEN
UPDATE
SET i = i10
OUTPUT deleted.i,
s.rn,
inserted.i;
GO
DROP TABLE #table;