>>>Boris,
>>>
>>>Good point about table variables!
>>>
>>>I don't think you got it right, though it's a very interesting solution. I added an identity field to the TestOrders table to check it.
>>>
>>>We need record 2 to become 5.
>>
>>And how we should handle the GAP?
>>Maybe 5 must becomes 2?
>
>See what I started to think in this thread and another on this topic. We somehow need to decrement orders to fill the gap. It's a bit tricky logic and I hadn't time to figure this completely.

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]