从一个SQL UPDATE INNER JOIN中更新多行并使用不同的值。

huangapple
go评论62阅读模式
英文:

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

问题

场景是一组包含与市场和市场上生产的商品相关的数据的表。在下面的基本示例中，你有冰，它生产水。

我有一个SQL查询，它运行良好并且性能良好（5毫秒左右）。它计算了生产X数量的水（它们都是商品）所需的冰的库存，但我只能更新其中一个商品的库存，在这种情况下，我正在更新水的库存，使其等于其先前的数量加上新生成的库存，但冰的库存保持不变。

有没有更好的方法可以构造这个SQL来在一次执行中更新两种商品的库存？
目前，我从研究中看到的唯一替代方法是多个UPDATE语句（我需要考虑在不同UPDATE语句执行之间是否有任何其他过程可以更改库存水平，从而使之前的计算无效）或从SELECT语句中获取输出并在应用程序代码中进行程序处理（我试图避免这样做，因为执行时间将更长）。

以下是内部SELECT语句的示例输出，其中newstock是正在生产的商品（goodid）的更新库存值，usedstock是用于生成其他商品的任何商品的新库存值（reqgoodid）。当前查询未更新已使用的库存。

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
英文:

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

答案1

得分: 2

此语句将在一个执行中更新Ice和Water的库存，使用单个带有多个SET子句的UPDATE语句。

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);
英文:

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);

答案2

得分: 0

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)
英文:

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)

huangapple
  • 本文由 发表于 2023年1月6日 13:24:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/75027224.html
  • mariadb
  • mysql
  • sql
  • sql-update
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定