Updating task order in hierarchical database after deletion

Data and Databases
1

Hey everyone, I’m working on a project with a task list database. It’s set up using the adjacency list model. Each task can have many subtasks and there’s a TaskOrder column to keep everything in the right order when it’s shown in a treeview.

I’m trying to figure out how to update the TaskOrder column when a task is deleted. Specifically, I need to select all the child nodes of a certain parent and update their order. I’m using MS-SQL 2005 for this.

Here’s what my table looks like:

Task Table
----------
TaskId
ParentTaskId
TaskOrder
TaskName

Does anyone know how to write an SQL query to do this? I’ve been scratching my head over it for a while now. Any help would be awesome! Thanks in advance.

2

hey ava, i’ve dealt with similar stuff before. have u tried using a CTE with ROW_NUMBER()? it can help reorder tasks after deletion. something like:

WITH TaskCTE AS (
SELECT TaskId, ParentTaskId, ROW_NUMBER() OVER (PARTITION BY ParentTaskId ORDER BY TaskOrder) AS NewOrder
FROM Task
WHERE ParentTaskId = @DeletedParentId
)
UPDATE Task SET TaskOrder = t.NewOrder
FROM Task INNER JOIN TaskCTE t ON Task.TaskId = t.TaskId

hope this helps!

3

ooh, interesting problem! have you considered using a recursive CTE to handle the whole hierarchy? it could be neat to update all levels at once. what if tasks can move between parents? that might add another layer of complexity. how deep does your task hierarchy usually go?

4

Having worked with similar hierarchical structures, I can share an approach that’s proven effective.

Consider using a Common Table Expression (CTE) to recursively select all child tasks of the deleted parent. Then, employ a ROW_NUMBER() function to reassign the TaskOrder values.

Here’s a rough outline of the SQL query:

WITH TaskCTE AS (
SELECT TaskId, ParentTaskId, TaskOrder
FROM Task
WHERE ParentTaskId = @DeletedTaskId
UNION ALL
SELECT t.TaskId, t.ParentTaskId, t.TaskOrder
FROM Task t
INNER JOIN TaskCTE tc ON t.ParentTaskId = tc.TaskId
)

UPDATE t
SET TaskOrder = r.NewOrder
FROM Task t
INNER JOIN (
SELECT TaskId, ROW_NUMBER() OVER (PARTITION BY ParentTaskId ORDER BY TaskOrder) AS NewOrder
FROM TaskCTE
) r ON t.TaskId = r.TaskId

This query should efficiently update the TaskOrder for all affected tasks. Remember to replace @DeletedTaskId with the actual ID of the deleted task.