May 30, 2012 at 6:14 pm
Hi,
I have a hierarchical data in my database, such as:
id_task|id_parent|value
1 | NULL | 0
2|1|0
3|2|0
4|3|0
5|4|0
6|4|0
What I want is when I update the Task 6 with value 10, his parents must have the sum of your childrens.
Like this:
id_task|id_parent|value
1|NULL|10
2|1|10
3|2|10
4|3|10
5|4|0
6|4|10
And when update the task 5 with value 3:
id_task|id_parent|value
1|NULL|13
2|1|13
3|2|13
4|3|13
5|4|3
6|4|10
I dont know how to use CTE with recursion for this case.
I tried to use Triggers but have a limite, 32, of nested recursion
So I created this ugly solution:
DECLARE @ID_PARENT INT
SET @ID_PARENT = (SELECT ID_PARENT FROM TASKS WHERE id_task = 6)
DECLARE @TOTAL_VALUE FLOAT
WHILE @ID_PARENT IS NOT NULL
BEGIN
SET @TOTAL_VALUE = (SELECT SUM(VALUE) FROM TASKS WHERE id_parent = @ID_PARENT)
UPDATE TASKS
SET value = @TOTAL_VALUE
WHERE id_task = @ID_PARENT
SET @ID_PARENT = (SELECT ID_PARENT FROM TASKS WHERE id_tasks = @ID_PARENT)
END
Does anyone know how to handle with problems like this one?
Tks
May 30, 2012 at 6:35 pm
DECLARE @Sample AS TABLE
(
id_task integer NOT NULL PRIMARY KEY CLUSTERED,
id_parent integer NULL,
value integer NOT NULL
);
INSERT @Sample
(id_task, id_parent, value)
VALUES
(1, NULL, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 0),
(5, 4, 0),
(6, 4, 0);
-- Update node 6 += 10
WITH rCTE AS
(
SELECT s.id_task, s.id_parent FROM @Sample AS s
WHERE s.id_task = 6
UNION ALL
SELECT s.id_task, s.id_parent
FROM @Sample AS s
JOIN rCTE ON s.id_task = rCTE.id_parent
)
UPDATE s
SET value = value + 10
FROM @Sample AS s
JOIN rCTE ON s.id_task = rCTE.id_task;
SELECT * FROM @Sample AS s;
-- Update node 5 += 3
WITH rCTE AS
(
SELECT s.id_task, s.id_parent FROM @Sample AS s
WHERE s.id_task = 5
UNION ALL
SELECT s.id_task, s.id_parent
FROM @Sample AS s
JOIN rCTE ON s.id_task = rCTE.id_parent
)
UPDATE s
SET value = value + 10
FROM @Sample AS s
JOIN rCTE ON s.id_task = rCTE.id_task;
SELECT * FROM @Sample AS s;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 7:08 pm
Brilliant and elegant.
Tks.
Is there a way for a Multiple Update without looping?
May 31, 2012 at 2:27 am
Assuming the changes to be made are in a table...(with the same sample data as before)
-- Table to hold set of changes
DECLARE @Updates AS TABLE
(
id_task integer NOT NULL PRIMARY KEY CLUSTERED,
value integer NOT NULL
);
-- Two changes
INSERT @Updates
(id_task, value)
SELECT
id_task = 6,
value_change = 10
UNION ALL
SELECT
id_task = 5,
value_change = 3;
-- Apply changes
WITH rCTE AS
(
SELECT s.id_task, s.id_parent, u.value
FROM @Sample AS s
JOIN @Updates AS u ON
u.id_task = s.id_task
UNION ALL
SELECT s.id_task, s.id_parent, rCTE.value
FROM @Sample AS s
JOIN rCTE ON s.id_task = rCTE.id_parent
)
UPDATE s
SET value = value + Summary.delta
FROM @Sample AS s
JOIN
(
SELECT
rCTE.id_task,
delta = SUM(rCTE.value)
FROM rCTE
GROUP BY
rCTE.id_task
) AS Summary ON Summary.id_task = s.id_task;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 31, 2012 at 5:03 am
I know you already have a solution that works for you so let me just ask what I would normally have asked...
1. How many rows are in your hierarchy?
2. How often do they change either by node position or by value (as you've requested)?
3. How soon after a change must the hierarchy table reflect the change?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2012 at 6:22 am
Tks Kiwi, works perfectly and fast.
Jeff,
Do you know the MS Project?
Think about a web application for Project Management and the manager can insert any hierarquical level of tasks.
And for conceptuals reasons, the parent tasks must be affected when his childrens is update (financials values).
May 31, 2012 at 6:16 pm
denisribeiro (5/31/2012)
Tks Kiwi, works perfectly and fast.Jeff,
Do you know the MS Project?
Think about a web application for Project Management and the manager can insert any hierarquical level of tasks.
And for conceptuals reasons, the parent tasks must be affected when his childrens is update (financials values).
You haven't really answered my question about how many rows but, considering what you've said, it's not many. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply