MySQL Forums
Forum List  »  Newbie

Re: Update a filed with sum from another table
Posted by: Phillip Ward
Date: September 13, 2023 08:23AM


I need to get the sum for the Job_No from the labour into the InvoiceSubtotal on the Job table, so the InvoiceSubtotal in the Job table will display 105.69

I would disagree.

As a rule, you should not store data that you can derive from other data in your database.
The reason for this is that if the individual items change for any reason and you don'y recalculate the Invoice-level total every, single time that happens (hint: using a Trigger) then your Invoice-level total will be wrong.

Instead, calculate the Invoice-level total as and when you need it.

You could do this just using a query ...

select sum( l.InvoiceSubtotal ) invoice_total 
from job j 
inner join labour l 
      on j.Job_No = l.Job_No
group by j.Job_No

... or, if you need to do this a lot, consider wrapping this query up in a View.

Regards, Phill W.

Options: ReplyQuote

Written By
Re: Update a filed with sum from another table
September 13, 2023 08:23AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.