Answered by:
Populate Parent/Child records with "Family" value

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 TSQL! 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
Question
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 Proposed as answer by Kalman TothModerator Monday, May 19, 2014 1:04 PM
 Marked as answer by Kalman TothModerator Monday, May 19, 2014 1:05 PM

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
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!!!
My blog
My TechNet articles
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
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

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 Proposed as answer by Kalman TothModerator Monday, May 19, 2014 1:04 PM
 Marked as answer by Kalman TothModerator Monday, May 19, 2014 1:05 PM

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

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

It was a bit of pseudocode, 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

It was a bit of pseudocode, 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! 
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 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
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!!!
My blog
My TechNet articles
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

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