>Something like this:
>

>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]
>
>

Hi Boris,

It doesn't work. Try @OriginalOrder = 9 and @NewOrder = 2. Don't run 2 updates in a row, play always with the same table.