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"