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.

DECLARE @TestOrders TABLE ([Order] int, OrderID int IDENTITY)
DECLARE @NewOrders  TABLE ([OldOrder] int, NewOrder int IDENTITY(1,1))

insert into @TestOrders values (1)
insert into @TestOrders values (2)
insert into @TestOrders values (3)
insert into @TestOrders values (4)
insert into @TestOrders values (5)
insert into @TestOrders values (6)
insert into @TestOrders values (7)
insert into @TestOrders values (8)
insert into @TestOrders values (9)


declare @OriginalOrder int
declare @NewOrder int

set @OriginalOrder = 2
set @NewOrder = 5
INSERT INTO @NewOrders (OldOrder)
SELECT [Order]
FROM @TestOrders
ORDER BY CASE WHEN [Order] = @OriginalOrder
                   then @NewOrder
              WHEN [Order] < @NewOrder
                   THEN [ORDER] 
              ELSE [ORDER] +1 END

UPDATE TestOrders SET [ORDER] = NewOrder.NewOrder
FROM @TestOrders TestOrders
INNER JOIN @NewOrders NewOrder ON TestOrders.[Order] = NewOrder.OldOrder
                                           
select * from @TestOrders ORDER BY [OrderID]

>>Hi everybody,
>>
>>The description of the problem can be found at http://forums.asp.net/p/1307742/2569516.aspx#2569516
>>
>>I'm now thinking that it's impossible to do in one step.
>>
>>Any ideas?
>>
>>Thanks in advance.
>
>

>DECLARE @TestOrders TABLE ([Order] int)
>DECLARE @NewOrders  TABLE ([OldOrder] int, NewOrder int IDENTITY(1,1))
>
>insert into @TestOrders values (1)
>insert into @TestOrders values (2)
>insert into @TestOrders values (3)
>insert into @TestOrders values (4)
>insert into @TestOrders values (5)
>insert into @TestOrders values (6)
>insert into @TestOrders values (7)
>insert into @TestOrders values (8)
>insert into @TestOrders values (9)
>
>
>declare @OriginalOrder int
>declare @NewOrder int
>
>set @OriginalOrder = 2
>set @NewOrder = 5
>INSERT INTO @NewOrders (OldOrder)
>SELECT [Order]
>FROM @TestOrders
>ORDER BY CASE WHEN [Order] = @OriginalOrder
>                   then @NewOrder
>              WHEN [Order] < @NewOrder
>                   THEN [ORDER]
>              ELSE [ORDER] +1 END
>
>UPDATE TestOrders SET [ORDER] = NewOrder.NewOrder
>FROM @TestOrders TestOrders
>INNER JOIN @NewOrders NewOrder ON TestOrders.[Order] = NewOrder.OldOrder
>
>select * from @TestOrders ORDER BY [Order]
>

>
>Please, next time when you give an example use table variables instead of real tables or temp tables :-)
>It is very easy to forget to drop them after the example ;-))