Re-sequencing a sequence

Posted on April 20, 2011

0


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


CREATE TABLE OrderedItems

(

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?

Advertisements
Posted in: SQL 2005, sql 2008