Results 1 to 2 of 2
  1. #1
    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.

    Code:
    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?

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    try with cte
    Code:
    ;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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •