Swap a Row Within a Table with MERGE




--DDL for table test
create table test (taskID int,	milestone varchar(50),	sortOrder int)

insert into test values (2861,'colour_thumbnails',1)
,(2866,'sent_to_print',	2)
,(2863,'page_approved', 3)
,(2860,'line_drawing', 4)
,(2864,'photo_taken',5)
,(2865,'photo_approved',6)
,(2859,'line_drawing_photo',7)

select * from test;
--Define the sortorder to be replaced
declare @sortOrder1 int=3, @sortOrder2 int=5


--SQL Server 2008 or above

MERGE test AS t
Using (SELECT taskID,milestone,CASE
                                 WHEN sortOrder = @sortOrder2 THEN @sortOrder1
                                 WHEN sortOrder = @sortOrder1 THEN @sortOrder2
                               END AS sortOrder
       FROM   test) AS src (taskID, milestone, sortOrder)
ON ( src.sortOrder = t.sortOrder )
WHEN Matched THEN
  UPDATE SET t.taskID = src.taskID,t.milestone = src.milestone,t.sortOrder = src.sortOrder; 



select * from test

drop table test




Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s