Re-sequencing a sequence

Posted on April 20, 2011


Let’s say you have a table that is ordered by a column but also contains a sequence number used for overiding the order sequence.

This table may look something like this



Item VARCHAR (50),
Sequence INT


Now, this initially is populated in sequence like so:

INSERT  INTO OrderedItems
        ( Item, Sequence )
VALUES  ( 'A', '1' ),
        ( 'B', '2' ),
        ( 'C', '3' ),
        ( 'E', '4' ),
        ( 'F', '5' )

You can see I deliberately left a gap for item ‘D’.
If I was to insert ‘D’ as a new item and made the sequence 6, what I really want now is to re-sequence the table, ordering by the Item.  If the column used for holding the sequence is part of a constraint or key of the table this isn’t easy. So how can we achieve this?
Well, as it turns out, we can use common table expressions to very easily do this.

WITH LFUpdate AS (
select *, ROW_NUMBER() OVER (ORDER BY Item) AS NewSeq
from OrderedItems

UPDATE LFUpdate SET Sequence = NewSeq ;

What do you think, useful?

Posted in: SQL 2005, sql 2008