我得到一个错误:错误编号:1242子查询返回一行以上
因为我的UPDATE
的子查询会产生重复值。我知道DISTINCT
和GROUP BY
可以删除SELECT
查询中的重复值。如何在UPDATE
查询中修复它。我的查询在这里
UPDATE
product_stock AS S
LEFT JOIN
product_purchase_item AS I
ON
S.product_id=I.product_id AND S.product_size=I.product_size
LEFT JOIN
product_purchases AS P
ON
I.product_purchase_item_id=P.product_purchase_item_id
SET
S.product_size_quantity=S.product_size_quantity+I.quantity
WHERE
S.product_id=?
AND
S.product_size=?
假设,如果我在SELECT
查询中转换它
SELECT
S.*
FROM
product_stock AS S
LEFT JOIN
product_purchase_item AS I
ON
S.product_id=I.product_id AND S.product_size=I.product_size
LEFT JOIN
product_purchases AS P
ON
I.product_purchase_item_id=P.product_purchase_item_id
查询返回
product_stock_id product_id product_size product_quantity10 216 1 110 216 1 111 216 5 1
但我需要(只需一个DISTINCT
关键字就可以在SELECT
查询中做到这一点)
product_stock_id product_id product_size product_quantity10 216 1 111 216 5 1
但是,如何在我的UPDATE
子查询中检索类似上述数据(唯一值)?在我的UPDATE
查询中,是否可以使用DISTINCT
关键字或类似的东西?
这样就可以了。product_purchases表之所以被包括在内,是因为它在您的查询中似乎是多余的吗?
UPDATE product_stock SET product_size_quantity = product_size_quantity + I.quantity
from product_stock
INNER JOIN (select product_id, product_size, sum(quantity) from product_purchase_item group by product_id, product_size) AS I
ON product_stock.product_id=I.product_id AND product_stock.product_size=I.product_size
WHERE product_stock.product_id=?
AND product_stock.product_size=?
如果数据确实相同,您可以简单地使用"min()"
,而无需按/不同分组
UPDATE
product_stock AS S
LEFT JOIN
product_purchase_item AS I
ON
S.product_id=I.product_id AND S.product_size=I.product_size
LEFT JOIN
product_purchases AS P
ON
I.product_purchase_item_id=P.product_purchase_item_id
SET
-- CHANGE HERE ===============================>
S.product_size_quantity=S.product_size_quantity+MIN(I.quantity)
WHERE
S.product_id=?
AND
S.product_size=?