Linking back to original parent record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linking back to original parent record

Author

Topic

ajn3341
Starting Member

5 Posts

Posted - 2010-04-17 : 13:03:29

I'm trying to figure out how to assign the original parent id from records that have an id linking back (IdXref) to the previous. This can be an unknown number of levels deep. As shown in the desired results below, id 5 links back to 3, and 3 then links back to 1, so the master parent id for 5 would be 1, and so on.


create table #Data (Id int null, IdXref int null) 
 
insert #Data (Id, IdXref) 
select 1, null 
union all 
select 2, 1 
union all 
select 3, 1 
union all 
select 4, 1 
union all 
select 5, 3 
union all 
select 6, 3 
union all 
select 7, 6 
union all 
select 8, null 
union all 
select 9, null 
union all 
select 10, 9 
 
/* 
Desired results: 
 
Id      Xref    MasterParent 
1                       1 
2       1               1 
3       1               1 
4       1               1 
5       3               1 
6       3               1 
7       6               1 
8                       8 
9                       9 
10      9               9 
11      10              9 
*/

The table that I am adding the MasterParent column to is very large. When records are added to the table, i understand I need to traverse back through the entire table until i find the MasterParent; however, I do not want to find and update the MasterParent for every single record, only those that are new that havent been assigned the MasterParent. I would also need to update those child records that had previously been assigned a MasterParent if matching parent records were loaded at a later date. Any suggestions?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-17 : 14:35:42

WITH CTE AS
(
SELECT Id, idXref, CAST(Id as varchar) as parent
FROM @Data
WHERE idXref IS NULL
UNION all
SELECT d.Id, d.IdXref, CAST(cast(c.parent as varchar) + cast(d.IdXref as varchar)as varchar) as parent
FROM @Data d JOIN CTE c ON d.IdXref = c.Id
)
SELECT Id, IdXref, SUBSTRING(parent,1,1) FROM CTE

Hope can help...but advise to wait pros with confirmation...

ajn3341
Starting Member

5 Posts

Posted - 2010-04-17 : 15:13:19

I have been trying out the recursive cte idea. However, since the table is so large, I cannot afford to find the original parent for every record every time. New records are added to the table with Id and IdXref, so each time i add records to the table and need to assign the MasterParent, I only want to traverse back into the entire table for those records newly added.
I would also need ability to traverse forward to assign an updated MasterParent to a child in the event that parent records were loaded after a child.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-18 : 06:18:05

erm....sifu tell me that if the level is not too deep...maybe won't that slow

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources