Skip to content Skip to sidebar Skip to footer

How To Reorder A Sql Table

I have a table that looks like this: RecNo | TaskDesc | SeqNumber 1 | test | 1 2 | test2 | 2 3 | test3 | 3 These items can be moved around using a reorder-ab

Solution 1:

You need to deal with moves up differently then moves down:

--Store the old value for performance and to simplify code.DECLARE@oldPositionint;
SELECT@oldPosition= SeqNum FROM tblTasks WHERE RecNo =@UpdatedRecNo;

UPDATE tblTasks
SET SeqNum =CASEWHEN RecNo =@UpdatedRecNoTHEN@newPosition--Put the record in the new position.WHEN@newPosition<@oldPositionAND SeqNum >=@newPositionAND SeqNum <@oldPositionTHEN SeqNum +1--Move the other rows out of the way when moving to a lower position.WHEN@newPosition>@oldPositionAND SeqNum <=@newPositionAND SeqNum >@oldPositionTHEN SeqNum -1--Move the other rows out of the way when moving to a higher position.ELSE SeqNum --No change.END;

This will pass over all of the records once and either: - Move it to the new sequence. - Move it to a higher sequence (if the moving record is moving down). - Move it to a lower sequence (if the moving record is moving up). - Leave it where it is.

If you do not want to touch all of the records, you can add:

WHERE   (@newPosition < @oldPosition AND SeqNum >= @newPosition AND SeqNum <= @oldPosition)
    OR  (@newPosition > @oldPosition AND SeqNum <= @newPosition AND SeqNum >= @oldPosition);

This would remove the need for the else clause.

Solution 2:

If your table is small, it might be better just to take a brute force approach:

with toupdate as (
      select t.*,
             row_number() over (orderby (casewhen RecNo =@UpdatedRecNothen@newPosition+0.5else seqnumber end)) as newseqnumber
      from tblTasks t
     )
update toupdate
     set seqnumber = newseqnumber
     where seqnumber <> newseqnumber;

Post a Comment for "How To Reorder A Sql Table"