MySQL - DELETE FROM with subquery

Question

im trying to execute this query but i'm getting error #1064.

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

But the following statement works

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

then how about delete from tableABC WHERE ID in (select ID from TABLEXYZ where qty = 0); — Vivek S.
– Vivek S., Commented Dec 6, 2014 at 6:34
what is the differences between my query? it's just lack of the backtick — chrizonline
– chrizonline, Commented Dec 6, 2014 at 7:37

chrizonline · Accepted Answer · 2014-12-06 07:55:31Z

2

Here is the answer:

to use a delete query with subquery which has a where condition, do the following:

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

answered Dec 6, 2014 at 7:55

chrizonline

4,99918 gold badges67 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RobP Over a year ago

THis is right -- the key part is that by naming the subquery 'b' you force it to get persisted to a temporary table (invisibly handled by the sql engine). To do deletes using a subquery, it's necessary that the subquery gets temporarily persisted.

Collectives™ on Stack Overflow

MySQL - DELETE FROM with subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related