• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

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
0
ARW
Asked:
ARW
1 Solution
 
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE Master
 SET total = (SELECT SUM(detail.balance) FROM  DETAIL WHERE detail.ID = Master.ID  )
WHERE total < .01
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now