Interesting recursive update

Transact-SQL
1

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

2

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.

3

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)

4

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
5

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...

6

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
7

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

8

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 :grinning:

9

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

10

hi

I have also tried the recursive cte

Mine is a bit different then Scotts

If it helps great
:slight_smile:
:slight_smile:

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

image