Results 1 to 2 of 2
04-19-10, 02:08 #1Registered User
- Join Date
- Sep 2005
Unanswered: 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 */
04-20-10, 04:55 #2Registered User
- Join Date
- Dec 2008
try with cte
;with cte (id,idxref, parent) as ( 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