Solved

Need to update parent fields with AVG of child fields

Posted on 2011-03-03
4
505 Views
Last Modified: 2012-05-11
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
Comment
Question by:Waterstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:mwiercin
ID: 35029999
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
 

Author Comment

by:Waterstone
ID: 35031504

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
 
LVL 3

Accepted Solution

by:
mwiercin earned 500 total points
ID: 35031874
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
 

Author Closing Comment

by:Waterstone
ID: 35032014
Thank you!  Works great and the examples you provided will help me in many ways.
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question