# Interesting recursive update

Hi,
I have an interesting recursive update problem. I have a table that has parent/child row relationships
TableA (ID int, Parent Int, Department char(3)). There can be 1 to 10 sub-parents (think child / parent / grand parent/ etc....).

With data something like this

ID Parent Department
1 NULL ABC
2 1 NULL
3 1 NULL
4 NULL DEF
5 4 NULL
6 5 NULL
7 5 NULL

Note that ID 6 and 7 are children of 5 and 5 is a child of 4
The recursive update needs to take the Department value of the top most parent and store it in all the children under that parent. So the resulting data would be

ID Parent Department
1 NULL ABC
2 1 ABC
3 1 ABC
4 NULL DEF
5 4 DEF
6 5 DEF
7 5 DEF

This is an inherited problem and I have a While loop solution that simulates a recursive update, but was curious to see a CTE solution if possible. I've tried a couple different CTE's and keep running into a maximum recursion has been exhausted error...

Thanks

It is interesting.

If you'll post directly usable data (CREATE TABLE with INSERT statements), I'll take a stab at doing the recursive SQL code.

This should give you enough to go on. Thanks

CREATE TABLE MYTABLE(ID int, ParentID int, Department Char(3))
Insert into MYTABLE
VALUES
(1, NULL, 'ABC'),
(2, 1, NULL),
(3, 1, NULL),
(4, NULL, 'DEF'),
(5, 4, NULL),
(6, 5, NULL),
(7, 5, NULL),
(8,NULL,'GHI'),
(9,8,NULL),
(10,8,NULL),
(11,10,NULL),
(12,10,NULL),
(13,10,NULL),
(14,13,NULL),
(15,13,NULL),
(16,13,NULL),
(17,13,NULL)

This will show you the parent Department. If you need help UPDATEing the parent Department in the children, just let me know.

``````;WITH cte_top_parent AS (
SELECT ID, ParentID, Department
FROM MYTABLE
WHERE ParentID IS NULL
UNION ALL
SELECT MT.ID, MT.ParentID, ctp.Department
FROM cte_top_parent ctp
INNER JOIN MYTABLE MT ON MT.ParentID = ctp.ID
)
SELECT *
FROM cte_top_parent``````

Thanks Scott, I got that far. It's recursive update that's giving me fits. I can't seem to get the update to recurse through the children (ie update the parent from the grand parent then update the child from the parent....etc) without throwing that max recursion error...

Hmm, confusing. Did you actually look at the results of my query?

At any rate, this will update the original table:

``````;WITH cte_top_parent AS (
SELECT ID, ParentID, Department
FROM MYTABLE
WHERE ParentID IS NULL
UNION ALL
SELECT MT.ID, MT.ParentID, ctp.Department
FROM cte_top_parent ctp
INNER JOIN MYTABLE MT ON MT.ParentID = ctp.ID
)
UPDATE MT
SET Department = ctp.Department
FROM MYTABLE MT
INNER JOIN cte_top_parent ctp ON ctp.ID = MT.ID AND MT.Department IS NULL``````

It's official! I'm an idiot! I was making it way more complicated! Thanks Scott!

Nah, sorry, I over-reacted.

It's normal to use the Department from the current row, but in this case we need to use the Department from the original row, the one that didn't have a parent (ParentID IS NULL). That's unusual, but it works perfectly here

Nope, I'm an idiot! Just couldn't see the forest through the trees! A bad case of visual paralysis!

hi

I have also tried the recursive cte

Mine is a bit different then Scotts

If it helps great

drop create data
``````use tempdb

go

drop table DATA
go

create table DATA
(
ID    int NULL,
Parent    int NULL,
Department  varchar(100)
)
go

insert into data select 1,NULL,'ABC'
insert into data select 2,1,NULL
insert into data select 3,1,NULL
insert into data select 4,NULL,'DEF'
insert into data select 5,4,NULL
insert into data select 6,5,NULL
insert into data select 7,5,NULL

select * from DATA
go
``````
SQL
``````; WITH rec_cte_grouping
AS (SELECT id,
parent,
department
FROM   data
WHERE  id = 1
UNION ALL
SELECT a.id,
a.parent,
CASE
WHEN a.parent IS NULL THEN a.department
ELSE b.department
END
FROM   data a
JOIN rec_cte_grouping b
ON a.id = b.id + 1)
SELECT *
FROM   rec_cte_grouping
``````
Results