SQL Stored Procedure: Reordering rows after delete

Question

In my database I have three tables as follows:

• Tasks (TaskID, TaskName, TaskDescription)
• TaskDetails (TaskID, subTaskPosition, SubTaskID)
• Subtasks (SubTaskID, Description)

I am trying to write a Stored Procedure to delete a SubTask completely and then re-order the other SubTasks for a Task.

Things to note:

• TaskDetails links Tasks to the constituent SubTasks.
• A SubTask may be referenced in a number of different Tasks.
• A Task should be made up of an ordered list of SubTasks... i.e. 1,2,3,4 NOT 1,3,4,5.

It is fairly easy to delete a SubTask and its links to a Task as follows:

• DELETE FROM TaskDetails WHERE SubTaskID = @subTaskID
• DELETE FROM SubTasks WHERE SubTaskID = @subTaskID

However, I cannot fathom how to re-order the other SubTasks in the TaskDetails table once the initial SubTask has been deleted. In English, I need to do the following - "For all the Tasks that have just had a SubTask deleted from it, subtract 1 from all of the subTaskPosition fields that occur after where the deleted row used to be".

Any help or pointers appreciated...

Gordon

Answer 1

I think you'll have to do an update for each task. This would do the trick (syntax is for SQL Server):

DECLARE @TaskID int
DECLARE @SubTaskPosition int

DECLARE curSubTaskPositionUpdate cursor fast_forward
FOR 
    SELECT TaskID, SubTaskPosition
    FROM TaskDetails 
    WHERE SubTaskID = @SubTaskID
OPEN curSubTaskPositionUpdate
FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
WHILE @@FETCH_STATUS = 0
    BEGIN   
        UPDATE TaskDetails
        SET SubTaskPosition = SubTaskPosition - 1
        WHERE TaskID = @TaskID
        AND SubTaskPosition > @SubTaskPosition

        FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
    END
CLOSE curSubTaskPositionUpdate
DEALLOCATE curSubTaskPositionUpdate

Note you do that before deleting from TaskDetails... and you probably want to wrap everything in a transaction.

Gordon Edit - I had to include my delete code within the update code to make this work, otherwise either (a) the delete occurred first and "where subtaskID = @subtaskID" returned nothing OR (b) I did the delete after the re-ordering and the re-order (correctly!) had no effect.

DECLARE @TaskID uniqueidentifier
DECLARE @SubTaskPosition int 

DECLARE curSubTaskPositionUpdate cursor fast_forward 
FOR  
    SELECT TaskID, SubTaskPosition 
    FROM TaskDetails  
    WHERE SubTaskID = @subTaskID 
OPEN curSubTaskPositionUpdate 
FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition 
WHILE @@FETCH_STATUS = 0 
   BEGIN    
-- Delete the subTask
DELETE FROM TaskDetails
WHERE TaskID = @TaskID
AND SubTaskPosition = @SubTaskPosition 

-- Update the other subTasks
        UPDATE TaskDetails
        SET SubTaskPosition = SubTaskPosition - 1
        WHERE TaskID = @TaskID
        AND SubTaskPosition > @SubTaskPosition

        FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
    END
CLOSE curSubTaskPositionUpdate
DEALLOCATE curSubTaskPositionUpdate

Answer 2

This should do it:

UPDATE  TaskDetails
SET     subTaskPosition = ROW_NUMBER() OVER(PARTITION BY TaskID  ORDER BY subTaskPosition) 
WHERE   TaskID IN(  SELECT  s.TaskID 
                    FROM    TaskDetails s 
                    WHERE   SubTaskID = @subTaskID)

No loops necessary... Note that this also works for case where multiple subtasks are missing or have already been deleted for the same task.

---

OK, here's the corrected version for SQL Server:

;WITH
  cteRows As
(
    SELECT  *,
    ROW_NUMBER() OVER(PARTITION BY TaskID  ORDER BY subTaskPosition) As NewPosition
    FROM    TaskDetails
)
UPDATE  cteRows
SET     subTaskPosition = NewPosition
WHERE   TaskID IN(  SELECT  s.TaskID 
                    FROM    TaskDetails s 
                    WHERE   SubTaskID = @subTaskID)

Sorry for the delay ...