mysql - 从 MySQL 中的 select 中删除

Question

我正在尝试从以下 SELECT 查询中删除结果行

select customer_ID, order_ID, t1, t2 
from web_order, items_table as a, items_table as b
  where (web_order.t1 = a.item_name and a.quantity) 
   and (web_order.t2 =  b.item_name and b.quantity)
   and ((a.quantity < b.quantity));

这是我的删除查询

delete from web_order where customer_ID in
(select customer_ID, order_ID, t1, t2 
 from web_order, items_table as a, items_table as b
   where (web_order.t1 = a.item_name and a.quantity) 
     and (web_order.t2 = b.item_name and b.quantity)
     and ((a.quantity < b.quantity)));

但我收到此错误

ERROR 1241 (21000): Operand should contain 1 column(s)

Answer 1

您无法在删除语句中从要删除的表中进行选择 - 这就像在迭代集合时尝试从集合中添加/删除项目一样。

这是 MySQL docs 的官方声明:

Subqueries

You cannot delete from a table and select from the same table in a subquery.

您需要使用 select 语句将 customer_ID 值插入到临时表中，然后在删除时从临时表中进行选择。

因此，它看起来像这样(我假设 Customer_ID 是 web_order 表中的 INT 列):

CREATE TEMPORARY TABLE CustomerIDs (Customer_ID INT NOT NULL)

INSERT INTO CustomerIDs 
select customer_ID from web_order, items_table as a, items_table as b
 where (web_order.t1 = a.item_name and a.quantity) 
 and (web_order.t2 = b.item_name and b.quantity)
 and ((a.quantity < b.quantity))

DELETE FROM web_order WHERE Customer_ID IN (SELECT Customer_ID FROM CustomerIDs)

DROP TABLE CustomerIDs