用不同的值从一个SQL Update INNER JOIN更新多行

Question

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

我有一个SQL查询工作良好，性能(5ms~)。它计算了生产X数量的水(它们都是商品)所需的冰的库存，但我只能更新其中一种商品的库存，在这种情况下，我更新了水的库存，使其等于之前的数量加上新生成的库存，但冰的库存保持不变。

是否有一个更好的方法，我可以构造这个SQL在一次执行中更新这两种商品的库存?目前，我从研究中看到的唯一替代方案是多个UPDATE语句(我需要考虑是否有任何其他进程可以在执行不同UPDATE语句之间的时间内改变库存水平，使先前的计算无效)或从SELECT语句中获取输出并在应用程序代码中以编程方式处理它(我试图避免这种情况，因为执行时间将大得多)。

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;

下面是内部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

Answer 1

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

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

Answer 2

在我对另一个答案的评论之后，我意识到我以前的一些尝试可能已经失败了，因为连接后的on干扰了一些SET命令，使用SET的CASE并删除连接的on使我的查询变得活跃，所有股票值现在都在正确更新。

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)