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?