SQL Stored Procedure: Reordering rows after delete
===

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