DECLARE @ItemCode nvarchar(100),@Index int
set @ItemCode=(select top 1 ItemCode from Bonus_Item where SortIndex >5 order by SortIndex asc )
set @Index=(select top 1 SortIndex from Bonus_Item where SortIndex >5 order by SortIndex asc )IF EXISTS (select top 1 SortIndex from Bonus_Item where SortIndex >5 order by SortIndex asc )
BEGIN
UPDATE Bonus_Item SET SortIndex =@Index WHERE SortIndex=5
UPDATE Bonus_Item SET SortIndex = 5 WHERE ItemCode=@ItemCode
END
declare @Index int
select top 1 @ItemCode= ItemCode,@Index= SortIndex rom Bonus_Item where SortIndex >5 order by SortIndex ascIF (@Index is not null)
BEGIN
UPDATE Bonus_Item SET SortIndex =@Index WHERE SortIndex=5
UPDATE Bonus_Item SET SortIndex = 5 WHERE ItemCode=@ItemCode
END这样试试?
资源共享:
SQL Server数据库的代码优化实例:
http://database.51cto.com/art/201108/285253.htm
我最后用联合查询写的UPDATE Bonus_Item SET SortIndex=A.SortIndex
FROM
(
SELECT @Index AS SortIndex,ItemCode FROM Bonus_Item WHERE SortIndex=@SortIndex
UNION all
SELECT @SortIndex AS SortIndex,ItemCode FROM Bonus_Item WHERE SortIndex=@Index
) A
WHERE Bonus_Item.ItemCode=A.ItemCode