October 16, 2003 at 10:17 am
Hi, I need help!!!
Can someone help me. I want to update a parent table with a minimum and maximum date from a child table. Example, the parent table has x records. Each x record has child records in another table with a date. I want to run a piece of T-SQL to update the parent records with the min and max date found in any related child records. If no child records are found, I want to update the parent values with nulls. Any ideas.
TIA
October 16, 2003 at 10:58 am
try this:
begin tran
Update Parent
Set ParentMaxDate = ISNull((Select Max(ChildDate) from Child where Child.FKParentID = Parent.ParentID),Null),
ParentMinDate = ISNULL((Select Min(ChildDate) from Child where Child.FKParentID = Parent.ParentID),NULL)
October 17, 2003 at 1:52 am
This might work
update p
set p.mindate = x.mindate,
p.maxdate = x.maxdate
from parent p
left outer join(SELECT keyid,min(date) as mindate, max(date) as maxdate
from child grouped by keyid) x
on x.keyid = p.keyid
Far away is close at hand in the images of elsewhere.
Anon.
October 17, 2003 at 3:14 am
David Burrows you are a star. It works perfectly. Not sure I really follow it but the main thing is it works. Thanks very much.
Thanks to mcneased also. It wasn't quite right as it only returned one value whereas I wanted a value for every parent, but thanks for trying.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply