DECLARE @TestOrders TABLE ([Order] int, OldOrder int) insert into @TestOrders values (1,1) insert into @TestOrders values (2,2) insert into @TestOrders values (3,3) insert into @TestOrders values (4,4) insert into @TestOrders values (5,5) insert into @TestOrders values (6,6) insert into @TestOrders values (7,7) insert into @TestOrders values (8,8) insert into @TestOrders values (9,9) declare @OriginalOrder int declare @NewOrder int set @OriginalOrder = 9 set @NewOrder = 2 UPDATE @TestOrders SET [ORDER] = CASE WHEN [Order] = @OriginalOrder THEN @NewOrder WHEN [Order] >= @NewOrder AND @OriginalOrder > @NewOrder AND [Order] <= @OriginalOrder THEN [ORDER] +1 WHEN [Order] <= @NewOrder AND [Order] > @OriginalOrder THEN [ORDER] -1 ELSE [ORDER] END select * from @TestOrders ORDER BY [Order]