Обновление по заказу с помощью cte не работает, почему?

Я хочу изменить последовательность таблицы путем пакетного обновления. Поскольку update не содержат order by, я использовал CTE, с предложением, сделал набор данных и выдал обновление по результату, ожидая, что он будет делать так, как я буду.
Но это обновление по 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|
3 3
sql-server tsql common-table-expression

3 ответа:

Но это обновление по Id, а не по моему упорядоченному набору. что в этом плохого обновление?

В коде нет упорядоченного набора. CTE не является и не может быть "заказан".

Вы должны использовать вычисленный RN в вашем обновлении, если вы этого не делаете, ваш код вообще не зависит от row_number (): 

declare @Test table(
    [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(cast(@serial as int) + RN as nvarchar(10))

select *
from @test
0

Ваше заявление об обновлении неверно. Вы обновляете 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

Смотрите живую демонстрацию на rextster.

1

Вы можете либо подсчитать, сколько строк в вашей таблице сначала и использовать переменную, либо выбрать большое число для своей вершины, чтобы выбрать всю таблицу, а затем упорядочить ее по порядковому номеру: 

;with Records as 
( 
    Select TOP 100000 
        Id
        , Serial 
    FROM [Test]
    where Id>@Id
    ORDER BY Serial
)
1