Tricky Update

Discussion in 'MySQL' started by Jamie T, Oct 12, 2015.

0
  1. #1
    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.
     
    Jamie T, Oct 12, 2015 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    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
     
    jestep, Oct 14, 2015 IP
  3. Jamie T

    Jamie T Member

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    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.
     
    Jamie T, Oct 18, 2015 IP