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

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

CodePudding user response：

This statement will update the stock of both Ice and Water in one execution, using a single UPDATE statement with multiple SET clauses.

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,
    a.stock = (
      select stock from market_locationgoods
      where location_id = b.locationid and good_id = b.reqgoodid
    ) - b.usedstock
where a.location_id = b.locationid
  and a.good_id in (b.goodid, b.reqgoodid);

CodePudding user response：

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)