MySQL - 使用子查询删除

Question

我尝试执行此查询，但收到错误 #1064。

delete from tableABC WHERE ID in (select ID from `TABLEXYZ` where `qty` = 0);

但是下面的语句有效

select * from tableABC WHERE ID in (select ID from TABLEXYZ where qty = 0);

Answer 1

答案如下:

要使用带有 where 条件的子查询的删除查询，请执行以下操作:

DELETE a FROM `tableABC` AS a JOIN (SELECT ID FROM `tableXYZ` WHERE `qty`=0) as b on b.ID = a.ID