Tricky Update

Hello.
I have 2 inventory tables. Spares, StockIssues.
They have a common id called 'Code'.
I am trying to update Spares.CurrentStock with the value of (Spares.CurrentStock - StockIssues.AmountTaken).
This is simple enough except if there are multiple entries of the same StockIssued.Code.
I somehow need to sum all values of StockIssues.AmountTaken with the same StockIssues.Code before updating Spares.
Have spent a long time looking for an answer. Help would be much appreciated.

So far I have :

SELECT (Spares.CurrentStock - sum(StockIssues.AmountTaken))
FROM StockIssues, Spares
where Spares.Code = StockIssues.Code
group by StockIssues.Code

Trying to get this into an update statement gives me problems.

Thanks.

Jamie.

 

You can run an update with a join at the same time. Something like this (untested):

UPDATE Spares SET Spares.CurrentStock = SUM(StockIssues.AmountTaken)
INNER JOIN StockIssues ON Spares.Code = StockIssues.Code
GROUP BY StockIssues.Code

 

Thanks very much jestep. I played around with that but could not get it to run. Eventually did it this way:

update spares, StockIssues
set spares.currentstock = currentstock - (select sum(amounttaken) from StockIssues where spares.code = StockIssues.code)
where spares.code = StockIssues.code

Thanks again and it was appreciated.
Jamie.