Solved

Need to update parent fields with AVG of child fields

Posted on 2011-03-03
4
489 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 85
mysql between clause 2 32
How to import sql database into mysql workbench 18 44
Unwanted output from my query 5 17
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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://…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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