MySQL Forums
Forum List  »  Newbie

Update a filed with sum from another table
Posted by: Paul Grimes
Date: September 13, 2023 03:35AM

I have a labour table with Id as Primary Key, Job_No and InvoiceSubtotal
This labour table can hold multiple values for the Job_No such as this

Id 1, Job_No = 1000000 InvoiceSubtotal = 95.37
Id 2, Job_No = 1000000 InvoiceSubtotal = 10.32

Then on my job table I have a field called also called Job_No which is the Primary key for this table and also InvoiceSubtotal field
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

As I have over 65K entries to edit, it would save me a lot of time with a query.

So I have tried something like this

INNER JOIN labour ON job.Job_No = labour.Job_No
SET job.InvoiceSubtotal as (SELECT ROUND(sum(InvoiceSubtotal),2) from labour where labour.Job_No = job.Job_No), 0)

Any help please?
Thank you

Options: ReplyQuote

Written By
Update a filed with sum from another table
September 13, 2023 03:35AM

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.