CODEWITHSUNDEEP
mysqlsum
elzaer
elzaer

Reputation: 729

MySQL Update table with sum value of another table

I have a query that I can't seem to manipulate to work in a SUM function in MySQL:

Here is what I want:

UPDATE account_seeds AS a
INNER JOIN b AS b ON b.accountID = a.accountID AND a.areaID = b.areaID
INNER JOIN b_seed AS s ON s.buildingID = b.buildingID
INNER JOIN seed_class AS c ON c.seedID = s.seedID
SET a.amount = a.amount + SUM(s.amount)
WHERE b.status='active' AND a.seedID = s.seedID

Now it obviously won't let me use the SUM in the update without separating it. I have tried joining select queries but can't quite get my head around it. The basic premise being that I have multiple buildings(rows) that has a seed value that will increase total seeds of that type in the area for a particular account. Without the sum it only updates one of the buildings that has a matching seed value

Upvotes: 0

Views: 1592

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

UPDATE 
    account_seeds AS a 
  INNER JOIN 
    ( SELECT b.accountID, b.areaID, s.seedID
           , SUM(s.amount) AS add_on 
      FROM b AS b 
        INNER JOIN b_seed AS s 
          ON s.buildingID = b.buildingID
        INNER JOIN seed_class AS c 
          ON c.seedID = s.seedID
      WHERE b.status = 'active' 
      GROUP BY b.accountID, b.areaID, s.seedID
    ) AS g
    ON  g.accountID = a.accountID 
    AND g.areaID = a.areaID
    AND g.seedID = a.seedID
SET 
    a.amount = a.amount + g.add_on ;

Upvotes: 2

Franco
Franco

Reputation: 171

Maybe you can use a nested query:

UPDATE account_seeds AS a 
INNER JOIN b AS b ON b.accountID = a.accountID AND a.areaID = b.areaID
INNER JOIN b_seed AS s ON s.buildingID = b.buildingID
INNER JOIN seed_class AS c ON c.seedID = s.seedID
SET a.amount = a.amount + (SELECT SUM(amount) FROM b_seed)
WHERE b.status='active' AND a.seedID = s.seedID

Can you try that?

Upvotes: 1

Related Questions