SQL Re-sequence Records

sql-server

Solution

You need to associate your re-numbered set with the IDs from your original set, otherwise you're updating the original ID with a sequence number for some other ID from your temp set:

UPDATE a
SET a.Sequence = temp.Sequence
FROM    
    tblA a JOIN
    (
        SELECT id, ROW_NUMBER() OVER (ORDER BY Sequence, ID) AS Sequence
        FROM dbo.tblA
    ) AS temp ON temp.ID = a.ID

Problem

I have a list of items that I need to re-sequence with no gaps. It starts out as: ``` ID Sequence 123 1 125 2 155 3 158 4 165 6 170 9 ``` I need to end up with (Note that Sequence 6 changes to 5 and Sequence 9 changes to 6) ``` ID Sequence 123 1 125 2 155 3 158 4 165 5 170 6 ``` I have tried using this update statement ``` UPDATE tblA SET tblA.Sequence = temp.Sequence FROM ( SELECT id , ROW_NUMBER() OVER ( ORDER BY Sequence, ID ) AS Sequence FROM dbo.tblA ) AS temp ``` but I just end up with ... ``` ID Sequence 123 1 125 1 155 1 158 6 165 6 170 6 ``` Pulling the select statement out of the update produces the correct results. Changing it to something like ``` UPDATE tblA SET tblA.Sequence = temp.NewSequence FROM ( SELECT id , ROW_NUMBER() OVER ( PARTITION BY id ORDER BY Sequence, id ) AS NewSequence FROM dbo.tblA ) AS temp ``` Produces the results ``` ID Sequence 123 1 125 1 155 1 158 1 165 1 170 1 ``` What am I doing wrong?

Original source