sql-server - 使用 cte 按顺序更新不起作用，为什么？

Question

我想通过批量更新来更改表的序列号。由于update不包含order by，所以我使用了CTE，with子句，做了一个数据集，并发布了结果更新，希望它会像我一样。
但它是由 Id 而不是我的有序集更新的。
此更新有什么问题？

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [Serial] [nvarchar](10) NOT NULL
)
insert into Test values
(1, 1001),
(2, 1002),
(3, 1003),
(4, 1004),
(5, 1005),
(6, 1006),
(7, 1003)

declare @serial int, @Id int
set @Id =3
select @serial = Serial from Test WHERE Id=@Id
declare @new_serial nvarchar(10);
select @new_serial = cast(@serial as nvarchar(10));

;with Records as 
( 
    Select Id, Serial 
    , ROW_NUMBER() over
    (
        order by serial
    ) as RN 
    FROM [Test]
    where Id>@Id
)
UPDATE Records set
    [Serial] = cast(@new_serial as int),
    @new_serial = cast(@new_serial as int)+1

插入后是这样的:

+--+----+
|1 |1001|
|2 |1002|
|3 |1003|
|4 |1004|
|5 |1005|
|6 |1006|
|7 |1003|

这是我们需要的:

+--+----+
|1 |1001|
|2 |1002|
|3 |1003|
|4 |1005|
|5 |1006|
|6 |1007|
|7 |1004|

Answer 1

你的更新声明是错误的。您正在更新 cte，然后将值设置为局部变量。我猜您希望更新将逐行执行，从而将每行中的 Serial 的值设置为先前的值 + 1。但是，sql 不是这样工作的。

要从您提供的输入中获得所需的输出，您不需要 cte，也不需要使用 row_number。你可以简单地这样做:

DECLARE @Id int = 3
UPDATE Test 
SET [Serial] = cast(Serial as int) + 1 
WHERE Id > @Id

检查:

SELECT Id, Serial
FROM Test

结果:

Id  Serial
1   1001
2   1002
3   1003
4   1005
5   1006
6   1007
7   1004

See a live demo on rextster.