Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Need to update parent fields with AVG of child fields

Hi,

I need a query to update a parent table with the average of related child fields from all child records.

Tables:  ParentTable, ChildTable
ParentTable.AVGCost1 = AVG(ChildTable.Cost1)
ParentTable.AVGCost2 = AVG(ChildTable.Cost2)
ParentTable.AVGCost3 = AVG(ChildTable.Cost3)

Joined on ParentTable.ParentTableKey = ChildTable.ParentTableKey

Would like to use a stored proc and pass it ParentTableKey

Thanks
0
Waterstone
Asked:
Waterstone
  • 2
  • 2
1 Solution
 
mwiercinCommented:
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

Open 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

Open 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.
0
 
WaterstoneAuthor Commented:

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
0
 
mwiercinCommented:
Hi,

Happy to help :) Following syntax would do the job.

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 IN(1,2,3,4,5)
   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 = IN(1,2,3,4,5)

Open in new window


You will find it hard to use it on variable length list of arguments through the stored procedure though. Easy if you know the ids and are dynamically constructing the query from programming language, way harder from within SQL.
0
 
WaterstoneAuthor Commented:
Thank you!  Works great and the examples you provided will help me in many ways.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now