No cigar, Boris.

Try with 7 and 5.

This one (by some great Indian guru) works fine:

DECLARE @DisplayOrders TABLE 
(
	ClientId int,
	DisplayOrder int,
    OrderID int identity
)

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


declare @OldDisplayOrder int 
declare @NewDisplayOrder int
set @OldDisplayOrder = 8
set @NewDisplayOrder = 6
if(@OldDisplayOrder > @NewDisplayOrder)
begin
	update @DisplayOrders set DisplayOrder = 
	case 
		when DisplayORder = @OldDisplayOrder then @NewDisplayORder
		else DisplayOrder + 1
	end
	where DisplayOrder >= @NewDisplayOrder and DisplayOrder <= @OldDisplayOrder
end
else
begin
	update @DisplayOrders set DisplayOrder = 
	case 
		when DisplayORder = @OldDisplayOrder then @NewDisplayORder
		else DisplayOrder - 1
	end
	where DisplayOrder <= @NewDisplayOrder and DisplayOrder >= @OldDisplayOrder
end

select * from @DisplayOrders order by DisplayOrder

http://forums.asp.net/p/1301701/2572218.aspx#2572218

>I want my 3 stars back :-)))))))
>

>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
>                                           THEN  [ORDER] +1
>                                      WHEN [Order] <= @NewOrder AND @NewOrder > @OriginalOrder AND [Order] >  @OriginalOrder
>                                           THEN  [ORDER] -1
>                                ELSE [ORDER] END
>select * from @TestOrders ORDER BY [Order]
>