skip to Main Content

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

2

Answers


  1. Chosen as BEST 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)
    

  2. 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);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search