Solved

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

Posted on 2006-11-06
2
409 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

867 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

15 Experts available now in Live!

Get 1:1 Help Now