By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE planRecords (Id int primary key, Plan_Id int, sequence_no int);
INSERT INTO planRecords (Id, Plan_Id, sequence_no) VALUES
(1100000000, 1167096719, 0),
(1100000001, 1167096719, 1),
(1100000002, 1167096719, 2),
(1100000003, 1167096719, 3),
(1100000004, 1167096719, 4),
(1100000005, 1167096719, 5);
6 rows affected
DECLARE @Id INT = 1100000004; -- this id
DECLARE @NewPosition INT = 1; -- needs to have this position
WITH RowToMove AS (
-- using cte instead of variables
SELECT Plan_Id, sequence_no AS OldPosition
FROM planRecords
WHERE Id = @Id
), RowsToUpdate AS (
-- columns used inside set and where clause of the update statement
SELECT Id, sequence_no, OldPosition
FROM planRecords
CROSS JOIN RowToMove
-- select rows that belong to same category and position between old and new
WHERE planRecords.Plan_Id = RowToMove.Plan_Id AND sequence_no BETWEEN
CASE WHEN OldPosition < @NewPosition THEN OldPosition ELSE @NewPosition END AND
CASE WHEN OldPosition > @NewPosition THEN OldPosition ELSE @NewPosition END
)
UPDATE RowsToUpdate SET sequence_no = CASE
WHEN Id = @Id THEN @NewPosition -- this is the row we are moving
WHEN OldPosition < @NewPosition THEN sequence_no - 1 -- row was moved down, move other rows up
WHEN OldPosition > @NewPosition THEN sequence_no + 1 -- row was moved up, move other rows down
END;
SELECT *
FROM planRecords
ORDER BY Plan_Id, sequence_no;
| Id | Plan_Id | sequence_no |
|---|---|---|
| 1100000000 | 1167096719 | 0 |
| 1100000004 | 1167096719 | 1 |
| 1100000001 | 1167096719 | 2 |
| 1100000002 | 1167096719 | 3 |
| 1100000003 | 1167096719 | 4 |
| 1100000005 | 1167096719 | 5 |