Update multiple rows from one SQL UPDATE INNER JOIN with different values

The scenario is a set of tables that contain data related to markets and goods produced at those markets. In the basic example below, you have Ice and this produces water.

I have a SQL query which works well and is performant (5ms~). It calculates the stock of Ice required to produce X amount of Water (they are both goods) but I am only able to update the stock of one of the goods, in this case I’m updating the stock of Water to be equal to its previous amount plus the newly generated stock but the stock of Ice remains unchanged.

Is there a better way I could structure this SQL to update the stock of both goods in one execution? At the moment the only alternatives I see from researching are multiple UPDATE statements (I’d need to think about if any other process could change stock levels in the time between the different UPDATE statements executing, invalidating earlier calculations) or getting the output from the SELECT statement and handling it programatically in the application code (I’m trying to avoid this as execution time will be much greater).

UPDATE market_locationgoods AS A
    INNER JOIN (SELECT locationgood.location_id AS locationid,
                       locationgood.good_id AS goodid,
                       reqgood.id AS reqgoodid,
                       reqstock.stock,
                       (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                           floor(reqstock.stock / goodsreq.mininput)) + locationgood.stock) AS newstock,
                       reqstock.stock - (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                                            floor(reqstock.stock / goodsreq.mininput)) * goodsreq.mininput) AS usedstock
                FROM market_locationgoods AS locationgood
                         LEFT JOIN market_goods AS goods ON goods.id = locationgood.good_id
                         LEFT JOIN market_goodsrequirement AS goodsreq ON goods.id = goodsreq.good_id
                         LEFT JOIN market_goods AS requiredgoods ON requiredgoods.id = goodsreq.requires_id
                         LEFT JOIN market_locationgoods AS reqstock on requiredgoods.id = reqstock.good_id AND
                                                                                     reqstock.location_id =
                                                                                     locationgood.location_id
                WHERE goods.type != 'Resource'
                  AND goods.name = 'Ice'
                  AND reqstock.stock is not null) AS B
    ON B.locationid = A.location_id and B.goodid = A.good_id
SET A.stock = B.newstock
WHERE A.location_id = B.locationid
  AND A.good_id = B.goodid;

Below is example output of the inner SELECT statement where newstock is the updated stock value for the good being produced (goodid) and usedstock is the new stock value for any good used (reqgoodid) to generate the other good. Used stock is not being updated in the current query.

locationid|goodid|reqgoodid|stock|newstock|usedstock
622994|1282|1283|482676.48|800|477676.48
623078|1282|1283|58383.36|800|53383.36
623610|1282|1283|149852.16|800|144852.16

Answer

After my comment on the other answer I realised some of my earlier attempts may have failed because of the ON following the join which was interfering with some of the SET commands, using CASE with SET and removing the ON for the join has brought my query to life, all stock values are updating correctly now.

UPDATE market_locationgoods AS A
    INNER JOIN (SELECT locationgood.location_id AS locationid,
                       locationgood.good_id AS goodid,
                       reqgood.id AS reqgoodid,
                       reqstock.stock,
                       (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                           floor(reqstock.stock / goodsreq.mininput)) + locationgood.stock) AS newstock,
                       reqstock.stock - (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                                            floor(reqstock.stock / goodsreq.mininput)) * goodsreq.mininput) AS usedstock
                FROM market_locationgoods AS locationgood
                         LEFT JOIN market_goods AS goods ON goods.id = locationgood.good_id
                         LEFT JOIN market_goodsrequirement AS goodsreq ON goods.id = goodsreq.good_id
                         LEFT JOIN market_goods AS requiredgoods ON requiredgoods.id = goodsreq.requires_id
                         LEFT JOIN market_locationgoods AS reqstock on requiredgoods.id = reqstock.good_id AND
                                                                                     reqstock.location_id =
                                                                                     locationgood.location_id
                WHERE goods.type != 'Resource'
                  AND goods.name = 'Ice'
                  AND reqstock.stock is not null) AS B
SET A.stock = (CASE when A.good_id = B.goodid then B.newstock
    when A.good_id = B.reqgoodid then B.usedstock
    end)
WHERE A.location_id = B.locationid
  AND A.good_id in (B.goodid, B.reqgoodid)