如何在Delete语句中使用Sum和Inner Join [英] How to use Sum and Inner Join in a delete statement

问题描述

我有一个名为TableName的表，我要从其产品数量SUM小于2的所有行中作为delete. 我需要inner join表oc_order_product和SUM具有相同product_id的值，然后在where子句中使用此SUM值来delete所有SUM小于2的行. 我现在正在使用以下查询:

I have a table called TableName which I want to delete from all rows that SUM of their product quantity is less than 2. I need to inner join table oc_order_product and SUM the values having the same product_id then use this SUM value in where clause to delete all rows with SUM less than 2. I am using the following query right now:

  Delete TablenName from TablenName
        INNER JOIN oc_order_product 
                     ON oc_order_product.product_id = TablenName.product_id
   where oc_order_product.quantity HAVING SUM(oc_order_product.quantity) < 2;

我遇到以下错误:

    #1064 - You have an error in your SQL syntax; check the manual that
 corresponds to your MariaDB server version for the right syntax 
to use near 'HAVING SUM(oc_order_product.quantity) < 2' at line 4

推荐答案

Hmmm. . .如果需要进行汇总，则需要在join:

Hmmm . . . If you need to do aggregation, you need to do it before the join:

Delete t
    from TablenName t join
         (select op.product_id, sum(op.quantity) as sumquantity
          from oc_order_product op
          group by op.product_id
         ) op
         on op.product_id = t.product_id and op.sumquantity < 2;

这篇关于如何在Delete语句中使用Sum和Inner Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！