如何在 UPDATE 查询中获得 DISTINCT/GROUP BY 行为 [英] How can I get DISTINCT/GROUP BY behavior in UPDATE query

问题描述

我收到一个错误:错误编号:1242 子查询返回超过 1 行

因为我的 UPDATE 的子查询产生重复值.我知道 DISTINCT 和 GROUP BY 可以删除 SELECT 查询中的重复值.如何在 UPDATE 查询中修复它.我的查询在这里

更新product_stock AS S左加入product_purchase_item AS I上S.product_id=I.product_id AND S.product_size=I.product_size左加入product_purchases AS P上I.product_purchase_item_id=P.product_purchase_item_id放S.product_size_quantity=S.product_size_quantity+I.quantity在哪里S.product_id=?和S.product_size=?

假设，如果我在 SELECT 查询中转换它

SELECTS.*从product_stock AS S左加入product_purchase_item AS I上S.product_id=I.product_id AND S.product_size=I.product_size左加入product_purchases AS P上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来自 product_stockINNER JOIN(选择 product_id, product_size, sum(quantity) from product_purchase_item group by product_id, product_size)AS ION product_stock.product_id=I.product_id AND product_stock.product_size=I.product_size哪里 product_stock.product_id=?AND product_stock.product_size=?

I get an Error: Error Number: 1242 Subquery returns more than 1 row

Because My UPDATE's subquery produce repeat values. I know DISTINCT and GROUP BY can remove repeat values in SELECT query.How can I fix it on UPDATE query. My query is Here

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=?

Suppose, If I convert it in SELECT query

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

The query return

product_stock_id  product_id   product_size product_quantity

10                 216          1            1 
10                 216          1            1
11                 216          5            1   

But I need(Just a DISTINCT keyword can do this in SELECT query)

product_stock_id  product_id   product_size product_quantity

10                 216          1            1 
11                 216          5            1   

But,how can I retrieve like above data(UNIQUE VALUES) in my UPDATE subquery? is it possible to use DISTINCT keyword or like anything in my UPDATE query?

解决方案

This should do the trick. Was there a reason for the product_purchases table to be included because it seems to be redundant in your query?

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=?

这篇关于如何在 UPDATE 查询中获得 DISTINCT/GROUP BY 行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！