如何在Delete语句中使用Sum和Inner Join并向其中添加第三个表 [英] How to use Sum and Inner Join in a delete statement and add a third table to it
问题描述
此问题是对以下此处回答的问题的补充 如何在删除中使用Sum和Inner Join声明
This question is an addition to the following question answered here How to use Sum and Inner Join in a delete statement
我将需要在以下查询中添加以下where语句WHERE oc_order_status.order_status_id IN ('3','5','17','19','20','23','25','26','29')
:
I will need to add the following where statement WHERE oc_order_status.order_status_id IN ('3','5','17','19','20','23','25','26','29')
to the following query:
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;
所有表共享具有相同值的同一product_id
列,并且所有表都位于同一数据库中.
All tables share the same product_id
column with same values and all tables are in the same database.
推荐答案
您可以使用FK product_id
生成产品和状态表的inner join
,并在where子句中过滤出order_status_id
.
You can do a inner join
of product and status table using the FK product_id
and filter out the order_status_id
in the where clause.
Delete t
from TablenName t join
(select op.product_id, sum(op.quantity) as quantity
from oc_order_product op
inner join oc_order_status os on os.product_id =op.product_id
where os.order_status_id IN (3,5,17,19,20,23,25,26,29)
group by op.product_id
) op
on op.product_id = t.product_id and op.quantity < 2;
这篇关于如何在Delete语句中使用Sum和Inner Join并向其中添加第三个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!