Solved

UPDATE a MASTER row with a SUM(detail-column)

Posted on 2006-11-06
2
396 Views
Last Modified: 2012-08-14
I thought I could do this

UPDATE MASTER SET total = (SUM(detail.balance))
INNER JOIN DETAIL ON detail.ID = master.ID
WHERE master.total < .01


I get an error that I cannot put an aggregate inside an UPDATE.  What is the proper way to handle this situation in SQL?

Thanks,
arw
0
Comment
Question by:ARW
2 Comments
 

Author Comment

by:ARW
ID: 17882677
On my WHERE clause,  I also am curious how I would UPDATE only those MASTER rows that had a total column that did not match the sum of the detail.balance column or would I have to just zero the total column and do something like this->

UPDATE MASTER SET total = total + detail.balance
INNER JOIN DETAIL ON detail.ID = master.ID

thanks,
arw
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17882706
UPDATE Master
 SET total = (SELECT SUM(detail.balance) FROM  DETAIL WHERE detail.ID = Master.ID  )
WHERE total < .01
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now