How to reorder rows using SQL (not swap)

sql-server, t-sql

Solution

DECLARE @id CHAR(1) = 'd';
DECLARE @newpos INT = 2;
DECLARE @oldpos INT;

BEGIN TRANSACTION
  SELECT @oldpos = Pos FROM Table WHERE id = @id;
  IF @newpos < @oldpos 
    UPDATE #Table SET Pos = Pos + 1 
     WHERE Pos >= @newpos AND Pos < @oldpos;
  ELSE
    UPDATE #Table SET Pos = Pos - 1 
     WHERE Pos >= @newpos AND Pos > @oldpos;

  UPDATE #Table SET Pos = @newpos
   WHERE Id = @id;
COMMIT TRANSACTION

Problem

I need to reorder items in a table as showed in the following example: ``` Original: Id Pos (int) --------- a 1 b 2 c 3 d 4 e 5 I need this result after moving d to the second row: Id Pos (int) --------- a 1 b 3 c 4 d 2 e 5 ``` As you can see, it is not a simple swap and I need to increase position by 1 after insert position, and leave the rows before insert the same. I tried to resolve it this way: ``` UPDATE [Table] SET Pos = 2 WHERE Id = 'd' MERGE INTO [Table] T USING ( SELECT ROW_NUMBER() OVER(ORDER BY Pos) AS Position, Id FROM [Table] ) S ON T.Id = S.Id WHEN MATCHED THEN UPDATE SET Pos = S.Position; ``` However after update two rows have the same position. It will probably not be sorted correctly. You can generate the example data using the script below: ``` CREATE TABLE [Table] ( Id char NOT NULL, Pos int NOT NULL, CONSTRAINT [PK_Table_Id] PRIMARY KEY CLUSTERED ([Id]) ) GO INSERT INTO [Table] (Id, Pos) VALUES ('a', 1),('b', 2),('c', 3),('d', 4),('e', 5) ```

Original source