我正在尝试通过在表DeliveryContent中添加产品数量和一条称为DeliveryQuantity的记录,同时内部连接两个记录StockCatalog.StockID = DeliveryContent.StockID,内部加入DeliveryContent.DeliveryID = Deliveries.DeliveryID,来添加表StockCatalog中名为Product Quantity的记录。到目前为止,我有这个:
UPDATE StockCatalog
SET ProductQuantity = (SELECT StockCatalog.ProductQuantity FROM StockCatalog INNER JOIN DeliveryContent on StockCatalog.StockID = DeliveryContent.StockID WHERE StockCatalog.ProductQuantity + DeliveryContent.DeliveryQuantity)
WHERE (SELECT DeliveryContent.DeliveryID FROM DeliveryContent INNER JOIN Deliveries on DeliveryContent.DeliveryID = Deliveries.DeliveryID)
但是,这似乎会用DeliveryContent中的DeliveryQuantity的一条记录来更新StockCatalog中的所有ProductQuantity记录。对不起,如果这令人困惑。
似乎对WHERE正在进行感到有些困惑。我猜这就是你想要的:
UPDATE StockCatalog sc SET ProductQuantity = ProductQuantity +
( SELECT DeliveryQuantity FROM DeliveryContent WHERE StockID=sc.StockID )
WHERE StockID in (select StockID from DeliveryContent);
如果每个StockID可以存在多个DeliveryContent,则应该用SUM(DeliveryQuantity)代替DeliveryQuantity。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句