By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE planRecords
(Id int, Plan_Id int, sequence_no int)
;
INSERT INTO planRecords
(Id, Plan_Id, sequence_no)
VALUES
(1132507748, 1167096719, 0),
(1102670655, 1167096719, 1),
(1166210290, 1167096719, 2)
;
3 rows affected
DECLARE @Id INT = 1102670655; -- item being moved
DECLARE @newPosition INT = 0; -- and its new position
DECLARE @Plan_Id INT;
DECLARE @oldPosition INT;
SELECT
@Plan_Id = Plan_Id,
@oldPosition = sequence_no
FROM planRecords
WHERE Id = @Id;
UPDATE planRecords SET sequence_no = CASE
WHEN Id = @Id THEN @newPosition -- item being moved gets new position without question
WHEN @oldPosition < @newPosition THEN sequence_no - 1 -- item moved up, other items must be pushed down
WHEN @oldPosition > @newPosition THEN sequence_no + 1 -- item moved down, other items must be pushed up
END
WHERE Plan_Id = @Plan_Id AND sequence_no BETWEEN -- only update items with same Plan_Id and position between old and new pos
CASE WHEN @oldPosition < @newPosition THEN @oldPosition ELSE @newPosition END AND
CASE WHEN @oldPosition > @newPosition THEN @oldPosition ELSE @newPosition END;
SELECT *
FROM planRecords
ORDER BY Plan_Id, sequence_no
| Id | Plan_Id | sequence_no |
|---|---|---|
| 1102670655 | 1167096719 | 0 |
| 1132507748 | 1167096719 | 1 |
| 1166210290 | 1167096719 | 2 |