Synchronize a Master column with its detail totals

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

I already know the above is incorrect, so here is my question

how would I 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
 
ARWAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
UPDATE MASTER SET total = (SUM(detail.balance) FROM detail where  detail.ID = master.ID )
WHERE master.total <>  (SUM(detail.balance) FROM detail where  detail.ID = master.ID )
or master.total is null
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi Angerl,
 Will it work ? it will give an error i think since u are trying to update using an aggregate function
0
 
ARWAuthor Commented:
This works though you have to add SELECT to the beginning of the subqueries

thanks again,
arw
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed, i forgot it in the copy/paste operation:

UPDATE MASTER SET total = (select SUM(detail.balance) FROM detail where  detail.ID = master.ID )
WHERE master.total <>  (select SUM(detail.balance) FROM detail where  detail.ID = master.ID )
or master.total is null

glad I could help, sorry for the missing SELECT...
0
 
ARWAuthor Commented:
no problem... the OR in the WHERE clause was something I would have forgot for sure without you having added it.
thanks
~aw
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.