Need to update parent fields with AVG of child fields

Use a derived aggregated table. Following updates everything:

UPDATE ParentTable pt JOIN (
  SELECT  
     ct.ParentTableKey, 
     AVG(ct.Cost1) as Cost1,
     AVG(ct.Cost2) as Cost2,
     AVG(ct.Cost3) as Cost3
   FROM ChildTable ct
   GROUP BY ct.ParentTableKey
) as derived ON(derived.ParentTableKey = pt.ParentTableKey) 
SET 
 pt.Cost1 = derived.Cost1,
 pt.Cost2 = derived.Cost2,
 pt.Cost2 = derived.Cost3

Select allOpen in new window

This will be quite a heavy query as it will process the whole ChildTable.

More selective version working on a single ParentTableKey value (you can also make it a subquery), I assume that __value is a declared store proc variable.

UPDATE ParentTable pt JOIN (
  SELECT  
     ct.ParentTableKey, 
     AVG(ct.Cost1) as Cost1,
     AVG(ct.Cost2) as Cost2,
     AVG(ct.Cost3) as Cost3
   FROM ChildTable ct
   WHERE ct.ParentTableKey = __value 
   GROUP BY ct.ParentTableKey
 
) as derived ON(derived.ParentTableKey = pt.ParentTableKey) 
SET 
 pt.Cost1 = derived.Cost1,
 pt.Cost2 = derived.Cost2,
 pt.Cost2 = derived.Cost3
WHERE pt.ParentTableKey = __value

Select allOpen in new window

For more than one record you can use IN instead of equality in where clauses (make sure you put WHEREs in both queries, one in derived table will limit amount of processing that needs to be done, and the main one - records updated.

Thank you.  Seems to work well.  Can you show me the query to follow this suggestion?

'For more than one record you can use IN instead of equality in where clauses (make sure you put WHEREs in both queries, one in derived table will limit amount of processing that needs to be done, and the main one - records updated.'

I've done similar things in MS SQL but it was long ago.

Thanks

Thank you!  Works great and the examples you provided will help me in many ways.