Solved

Need to update parent fields with AVG of child fields

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 169
PHP loop not working 4 62
Insert data into database 2 42
when to use sequences in mysql 4 29
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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