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

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
ARWAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
UPDATE Master
 SET total = (SELECT SUM(detail.balance) FROM  DETAIL WHERE detail.ID = Master.ID  )
WHERE total < .01
0
 
ARWAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.