If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > Updating child records with master parent

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Sep 2005
Posts: 3
Updating child records with master parent

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

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 every time, only those that are new that havent been assigned the MasterParent. When new records are added, I would also need to update those child records that had previously been assigned a MasterParent (most likely the xref since the parent record did not exist in the table yet) if matching master parent records were loaded at a later date. So if its a new child, i need to traverse back through the table to get the original parent - if new parent, i would need to update any related child records with the master parent.

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 final table data: 

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 
. Any suggestions?
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Dec 2008
Posts: 135
try with cte
;with cte (id,idxref, parent)
select *,null from #data
union all
select c.id,p.idxref,p.id 
from #data p 
inner join cte c on c.idxref = p.id and p.id <> c.id

select c.id,d.idxref,(isnull(c.parent,c.id)) AS masterparent from cte c
inner join #data d on d.id = c.id
where c.idxref is null order by c.id
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On