Geek asks and answers

SQL Repeat Records

I have a list of elements that I need to re-sequence without spaces. It starts with:

ID   Sequence
123    1
125    2
155    3
158    4
165    6
170    9

I need to finish (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 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 ended up with ...

ID   Sequence
123    1
125    1
155    1
158    6
165    6
170    6

Pulling the select statement from 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 results

ID   Sequence
123    1
125    1
155    1
158    1
165    1
170    1

What am I doing wrong?

+3
source share
1 answer

, :

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
+4

More articles:


All Articles