>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 = 2 >set @NewOrder = 5 > >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 @NewOrder > @OriginalOrder AND [Order] > @OriginalOrder > THEN [ORDER] -1 > ELSE [ORDER] END >select * from @TestOrders ORDER BY [Order] > >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 @NewOrder > @OriginalOrder AND [Order] > @OriginalOrder > THEN [ORDER] -1 > ELSE [ORDER] END >select * from @TestOrders ORDER BY [Order] > >Seems to work great, thanks.