MySQL Update table with sum value of another table

Question

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

ypercubeᵀᴹ · Accepted Answer · 2012-07-08 14:17:01Z

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 ;

Franco · Accepted Answer · 2012-07-08 12:04:28Z

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?

Collectives™ on Stack Overflow

MySQL Update table with sum value of another table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related