sql 交换指定行与相临行的列值

declare @Name1 nvarchar(50),@Name2 nvarchar(50),@ID1 int,@ID2 int
select @Name1=Name,@ID1=CustomerID from dbo.customTable where CustomerID=519 order by CustomerID
select @Name2=Name,@ID2=CustomerID from dbo.customTable where CustomerID<519 order by CustomerID
update dbo.customTable set Name=@Name2 where CustomerID=@ID1
update dbo.customTable set Name=@Name1 where CustomerID=@ID2

 

示例数据

CustomerID	Name	ModifiedDate
15	Budget Toy Store	2004-10-13 11:15
33	Global Sports Outlet	2004-10-13 11:15
51	Ideal Components	2004-10-13 11:15
69	Online Bike Catalog	2004-10-13 11:15
87	Rich Department Store	2004-10-13 11:15
105	Volume Bike Sellers	2004-10-13 11:15
123	First Supplies	2004-10-13 11:15
141	Rental Gallery	2004-10-13 11:15
159	Uncompromising Quality Co	2004-10-13 11:15
177	Plastic Products Manufacturers	2004-10-13 11:15
195	Inexpensive Parts Shop	2004-10-13 11:15
213	Fast Bike Works	2004-10-13 11:15
231	Helmets and Cycles	2004-10-13 11:15
249	Helpful Sales and Repair Service	2004-10-13 11:15
267	Cycle Parts and Accessories	2004-10-13 11:15
285	Nearest Bike Store	2004-10-13 11:15
303	Seaside Bike Works	2004-10-13 11:15
321	Second Bike Shop	2004-10-13 11:15
339	Major Bicycle Store	2004-10-13 11:15
357	Quality Bike Sales	2004-10-13 11:15
375	Region Bike Company	2004-10-13 11:15
393	Gears and Parts Company	2004-10-13 11:15
411	Kids and Adults Cycle Shop	2004-10-13 11:15
429	Unusual Bicycle Company	2004-10-13 11:15
447	Cross-town Parts Shop	2004-10-13 11:15
465	Expert Cycle Store	2004-10-13 11:15
483	Fitness Bike Accessories	2004-10-13 11:15
501	Jumbo Bikes	2004-10-13 11:15
519	List Price Catalog Company	2004-10-13 11:15
537	Preferable Bikes	2004-10-13 11:15
555	Twin Cycles	2004-10-13 11:15
573	Nationwide Supply	2004-10-13 11:15
591	Fitness Discount Store	2004-10-13 11:15
616	Eastward Bike Accessories	2004-10-13 11:15
634	Sensible Sports	2004-10-13 11:15
640	Liquidation Sales	2004-10-13 11:15
658	Mass Market Bikes	2004-10-13 11:15
664	Eastside Cycle Shop	2004-10-13 11:15
682	Bike Part Wholesalers	2004-10-13 11:15
688	Popular Bike Lines	2004-10-13 11:15