SQL Server Performance

Updating child with master parent record

Discussion in 'SQL Server 2005 General Developer Questions' started by ajn3341, Apr 17, 2010.

  1. ajn3341 New Member

    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 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
    */

    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 (most likely the xref since the parent record did not exist in the table yet) if matching parent records were loaded at a later date. Any suggestions?
  2. satya Moderator

    Welcome to the forums.
    How big is the table?
    How frequently this udpate needs to happen?
  3. ajn3341 New Member

    The table is around 400 million records. Since assigning the Master parent is a new requirement, i will need to assign it for all records in the table to update the history. New records are loaded once a day - I'll need to: 1.) Find Master Parent for new records (digging back into the table to get the highest parent if necessary) and 2.) Adjust any child records that link up to a new parent.
  4. mguissine New Member

    Simple way to solve your problem is to use CTE (if you are on 2005 and up of course), here is an example:;with MasterRef(Id, IdXref ,MasterParent) As ( SELECT Id, IdXref, Id as MasterParent FROM #Data WHERE IdXRef IS NULL UNION ALL SELECT a.Id, a.IdXref, case when b.IdXref Is Null then b.Id else b.MasterParent end as MasterParent FROM #Data a INNER JOIN MasterRef b ON a.IdXref = b.Id )SELECT Id, IdXref, MasterParentFROM MasterRef order by Id;GO Not sure if it will work nicely with 400M records table but you can try.

Share This Page