none
Populate Parent/Child records with "Family" value

    Question

  • Hi,

    I typically do the task I'm asking assistance with in Excel and it works great... the only problem is that it's VERY slow and takes a lot of work when over 1 million rows as you must split it up and yeah just painful! But what I'm trying to accomplish is pretty straight forward for Excel, however escapes me in T-SQL! I have an ordered number that is sorted asc. I have a flag that indicates a record is the "Parent". I want to update the family value for the "Parent" and all sequential records until the next "Parent" records is reached. So here is an example in Excel.

    Using the following formula in cell C2 and pasting all the way to the last record, I can easily(for smaller sets anyway) create my desired results.

    =IF(B2="Y",A2,C1)

    Number	Flag	Family
    1	Y	1
    2		1
    3		1
    4		1
    5		1
    6		1
    7		1
    8		1
    9	Y	9
    10		9
    11	Y	11
    12		11
    13		11
    14	Y	14
    15		14
    16		14
    17		14
    18		14
    19		14


    • Edited by UOgod619 Friday, May 16, 2014 2:00 AM Added more info
    Friday, May 16, 2014 1:58 AM

Answers

  • I have an article on this exact topic

    Fixing Missing Data Based on Prior Row Information

    It shows the concept of using OUTER APPLY to achieve desired functionality.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 16, 2014 2:19 AM
  • I have an article on this exact topic

    Fixing Missing Data Based on Prior Row Information

    It shows the concept of using OUTER APPLY to achieve desired functionality.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    I wish I knew how to apply this OUTER APPLY solution to an update statement so I could use. Would this be more efficient and faster than the solution provided by RyanAB? I have millions of records and NOT enough time... the fastest solution is definitely desired!!!

    I ended up using this as a solution as it was the fastest. I first did:

    SELECT a.Number, M.Number 'Family' into FamilyTable FROM Table a
    OUTER APPLY (
        SELECT TOP  (1) *
        FROM Table b
        WHERE a.Number >= b.Number and b.[Flag] = 'Y'
        ORDER BY  b.[Number] DESC) M
    ORDER BY 1, 2

    Then updated the original like so:

    update a set a.Family = b.Family
      FROM Table a inner join FamilyTable b
      on a.Number = b.Number

    Thanks to all!!!

    • Marked as answer by UOgod619 Friday, May 16, 2014 5:44 AM
    Friday, May 16, 2014 5:44 AM

All replies

  • Don't have details on your table so little bit of fiddling but something along the lines of...

    update <excel> a

    set family = (select top 1 id from <excel> b where a.number >= b.number and b.flag is not null order by number desc)

    • Proposed as answer by Naomi NModerator Friday, May 16, 2014 2:16 AM
    • Marked as answer by UOgod619 Friday, May 16, 2014 2:49 AM
    • Unmarked as answer by UOgod619 Friday, May 16, 2014 5:45 AM
    Friday, May 16, 2014 2:05 AM
  • I have an article on this exact topic

    Fixing Missing Data Based on Prior Row Information

    It shows the concept of using OUTER APPLY to achieve desired functionality.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 16, 2014 2:19 AM
  • Don't have details on your table so little bit of fiddling but something along the lines of...

    update <excel> a

    set family = (select top 1 id from <excel> b where a.number >= b.number and b.flag is not null order by number desc)

    Your SQL doesn't work, however I am not sure about the logic. It is currently running and I will report on that in a second. This is what I ran to make it work using your provided logic:

    update a

    set family = (select top 1 number from table b where a.number >= b.number and b.flag != '' order by number desc)

    from table b

    Friday, May 16, 2014 2:21 AM
  • Don't have details on your table so little bit of fiddling but something along the lines of...

    update <excel> a

    set family = (select top 1 id from <excel> b where a.number >= b.number and b.flag is not null order by number desc)

    Your SQL doesn't work, however I am not sure about the logic. It is currently running and I will report on that in a second. This is what I ran to make it work using your provided logic:

    update a

    set family = (select top 1 number from table b where a.number >= b.number and b.flag != '' order by number desc)

    from table b

    Also, please note that I nowhere indicated that there was an ID field. However I am thankful for your reply and assistance!
    Friday, May 16, 2014 2:22 AM
  • It was a bit of pseudo-code, you needed to fill in the blanks...

    Yes, my apologies, "id" should have been "number". As we're updating the actual table we're running from, you just need to state "update table a" rather than a "from table [a]"... you didn't really name your table "table" did you?


    PS No claims as to performance, if you are wanting a longer term solution then reading Naomi's link may prove beneficial. I just gave a quick answer to your question
    • Edited by RyanAB Friday, May 16, 2014 2:47 AM
    Friday, May 16, 2014 2:37 AM
  • It was a bit of pseudo-code, you needed to fill in the blanks...

    Yes, my apologies, "id" should have been "number". As we're updating the actual table we're running from, you just need to state "update table a" rather than a "from table [a]"... you didn't really name your table "table" did you?


    Well actually that did NOT work for me. I had to update alias as update tableName did NOT work. No, just calling them table for the discussion. Your logic is perfect though and I achieved the desired results! Thanks sir!
    Friday, May 16, 2014 2:49 AM
  • I have an article on this exact topic

    Fixing Missing Data Based on Prior Row Information

    It shows the concept of using OUTER APPLY to achieve desired functionality.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    I wish I knew how to apply this OUTER APPLY solution to an update statement so I could use. Would this be more efficient and faster than the solution provided by RyanAB? I have millions of records and NOT enough time... the fastest solution is definitely desired!!!
    Friday, May 16, 2014 2:57 AM
  • I have an article on this exact topic

    Fixing Missing Data Based on Prior Row Information

    It shows the concept of using OUTER APPLY to achieve desired functionality.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    I wish I knew how to apply this OUTER APPLY solution to an update statement so I could use. Would this be more efficient and faster than the solution provided by RyanAB? I have millions of records and NOT enough time... the fastest solution is definitely desired!!!

    I ended up using this as a solution as it was the fastest. I first did:

    SELECT a.Number, M.Number 'Family' into FamilyTable FROM Table a
    OUTER APPLY (
        SELECT TOP  (1) *
        FROM Table b
        WHERE a.Number >= b.Number and b.[Flag] = 'Y'
        ORDER BY  b.[Number] DESC) M
    ORDER BY 1, 2

    Then updated the original like so:

    update a set a.Family = b.Family
      FROM Table a inner join FamilyTable b
      on a.Number = b.Number

    Thanks to all!!!

    • Marked as answer by UOgod619 Friday, May 16, 2014 5:44 AM
    Friday, May 16, 2014 5:44 AM
  • You don't need to put this into separate table, e.g.

    ;with cte as (SELECT a.Number, M.Number  as [CorrectFamily]  FROM Table a
    OUTER APPLY (
        SELECT TOP  (1) *
        FROM Table b
        WHERE a.Number >= b.Number and b.[Flag] = 'Y'
        ORDER BY  b.[Number] DESC) M
    WHERE a.Family IS NULL)
    
    update cte SET Family = CorrectFamily


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 16, 2014 3:56 PM