Move Up Or Move Down Or Move All the Way Up Or All The Way Down — T-SQL Solution With Merge

Come across this question from this thread which is similar as one custom sequencing issue I have an answer before.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d34ccbc-5839-45af-9eaf-e8756d530a0f/sql-stored-procedure-help-please-help-me-complete-this-stored-proc-having-trouble-getting-around

Here is a solution I modified with one existing solution I had early:

https://jingyangli.wordpress.com/2013/06/17/custom-sequence-numbering-2-row_number-and-merge-solution/

Here is the sample code:

--DDL for table ToDoList
If Exists ( SELECT * FROM sys.tables JOIN sys.schemas
 ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
 Begin
 Drop table ToDoList
 End

Create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence int)

insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)


 --Before new ordering
select * from ToDoList order by todoSequence ;


--Change parameters here!!!!!!!!!!

declare @personID int = 1,
 @todoSequence int =3,
 @MoveRequest int = 1
 --Move Request 0 = All the way down, 1 = One down, 2 = One up, 3 = All the way up

Merge ToDoList TDL
 USING (
 SELECT todoID,
 ROW_NUMBER() OVER(ORDER BY
 CASE WHEN todoSequence = @todoSequence THEN
 CASE WHEN @MoveRequest=0 THEN (SELECT MAX(todoSequence) FROM ToDoList WHERE PersonID=@PersonID)+1
 WHEN @MoveRequest=1 THEN todoSequence+1
 WHEN @MoveRequest=2 THEN todoSequence-1
 WHEN @MoveRequest=3 THEN 0
 END
 ELSE toDoSequence
 END,
 CASE
 WHEN @MoveRequest<=1 AND todoSequence = @todoSequence Then todoSequence+1
 WHEN @MoveRequest>1 AND todoSequence = @todoSequence
 THEN 0 ELSE todoSequence-1
 END )
 AS RowNum

FROM ToDoList WHERE PersonID=@PersonID ) as Src (todoID, RowNum)
 On TDL.todoID=Src.todoID

WHEN Matched Then
 UPDATE SET todoSequence=Src.RowNum;

select * from ToDoList
order by todoSequence

MERGE a subset of the target table — Pitfall and Options

I have used MERGE for sometime now. It is a very useful feature added to SQL Server 2008.  I have used MERGE to come up with some solutions to my real production code.

When it is time to use a subset of the target table to manipulate, it is attempting to add the condition to the ON clause like target.id=2 to filter our some records for the target table. But this is not the correct way to do it.

I have run into this issue for a question posted at MSDN:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/38e82c13-af2f-45d2-98f6-a77c686ef6f4/update-column-with-values-from-the-same-column#4d037492-f0ec-4c26-aaf3-bfcc4b52aada

Tom Cooper pointed out the issue in another related thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a2ad67c-bff5-4dba-bf71-1b8004be90ce/update-column-with-values-from-the-same-column#6ebd6bdb-7bd9-4989-bc89-62d378a6adc2

To better understand this issue, I serached to find a detailed example from Paul White to discussing about this issue: http://dba.stackexchange.com/questions/30633/merge-a-subset-of-the-target-table

Here is a fully documented link from MSDN:  http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx

and the definition of MERGE documentation: http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx

To solve the problem, we have three options: Use the filter at the WHEN clause; use an UPDATABLE View; use CTE.

I would like to use WHEN if I want a quick fix. To make it more clear, I will write the code starting with a CTE block before the MERGE keyword and use the CTE  as target table.