Schwann Schwann - 4 months ago 29
MySQL Question

How to use Sum and Inner Join in a delete statement

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;


Which I am getting the following error:

#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


I will also need to check the status or the orders in column
order_status_id
from a third table called
oc_order_status
with something like
WHERE oc_order_status.order_status_id IN ('3','5','17','19','20','23','25','26','29')

Answer Source

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 quantity
          from oc_order_product op
          group by op.product_id
         ) op
         on op.product_id = t.product_id and op.quantity < 2;