I hate you :-)))))))))))))))))))))))))))))))
But I WANT my stars back :o))))
I thought that the deal was all to be in ONE statement :-)))))

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]