CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Ahmad Ebrahimi
Ahmad Ebrahimi

Reputation: 235

Update by order using cte not working, why?

I want to change serial of table by batch update. Since update do not contain order by, I used CTE, with clause, made a data set and issued update on the result, expected that it will do as I will.
But it update by Id not by my ordered set.
what is wrong with this update?

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

Here is what after insert exists:

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

Here is what we need:

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

Upvotes: 3

Views: 1005

Answers (3)

PreQL
PreQL

Reputation: 358

You can either count how many rows in your table first and use a variable or pick a large number for your TOP to select the entire table and then order that by your serial:

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

Upvotes: 0

sepupic
sepupic

Reputation: 8687

But it update by Id not by my ordered set. what is wrong with this update?

You have no ordered set in your code. CTE is not and cannot be "ordered".

You should use your calculated RN in your update, if you don't, your code does not depend on row_number() at all:

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

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Your update statement is wrong. You are updating the cte and then setting a value to a local variable. I'm guessing you expect that the update will be executed row by row, thus setting the value of Serial in each row to the previous value + 1. However, that's not how sql works.

To get your desired output from the input you provided you do not need a cte nor do you need to use row_number. You can simply do this:

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

Check:

SELECT Id, Serial
FROM Test

Results:

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

See a live demo on rextster.

Upvotes: 1

Related Questions