Subquery fails in mysql

Question

I have a subquery as follows : which will select the id's according to the condition first and delete

the records ,

    Delete from post_master_user_map WHERE id IN
(SELECT id FROM `post_master_user_map` WHERE posted_by_user_id=110);

But it gives me the following error :

You can't specify target table 'post_master_user_map' for update in FROM clause

What is wrong with this ? thanks in advance .

UPDATE

This also fails , I dont why

DELETE FROM `post_master_user_map` WHERE `reshare_id` in (SELECT id FROM `post_master_user_map` WHERE 
posted_by_user_id=110);

Fathah Rehman P · Accepted Answer · 2014-09-08T12:11:07.020

1

This error occurs when you try to modify a table and select from the same table in sub query.

Anyway to solve that error change your query as follows

Delete from post_master_user_map WHERE posted_by_user_id=110;

For the updated query(in your question) use following

    DELETE t1 FROM post_master_user_map as t1 INNER JOIN 
post_master_user_map as t2 ON t1.reshare_id=t2.id and t2.posted_by_user_id=110

edited Sep 08 '14 at 12:11

answered Sep 08 '14 at 11:11

Fathah Rehman P

DELETE FROM `post_master_user_map` WHERE `reshare_id` in (SELECT id FROM `post_master_user_map` WHERE posted_by_user_id=110); is also fails – KTM Sep 08 '14 at 11:16
"This error occurs when you try to modify a table and select from the same table in sub query." In this case you are modifying 'post_master_ser_map' and you are selecting from same 'post_master_ser_map' in subquery – Fathah Rehman P Sep 08 '14 at 11:19
Waht i need is select the id's first from the query : SELECT id FROM post_master_user_map WHERE posted_by_user_id=110 And then delete with the query : DELETE FROM post_master_user_map WHERE reshare_id in (id's of the above result); – KTM Sep 08 '14 at 11:20
1

DELETE t1 FROM post_master_user_map as t1 INNER JOIN post_master_user_map as t2 ON t1.reshare_id=t2.id and t2.posted_by_user_id=110 – Fathah Rehman P Sep 08 '14 at 11:45
Do that two same , will it can apply the updated question above ? OKay update the answer , i will accept it . – KTM Sep 08 '14 at 12:04

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

By this MySQL DELETE FROM with subquery as condition : MySQL does not allow the table you're deleting from be used in a subquery for the condition.

edited May 23 '17 at 12:12

Community

answered Sep 08 '14 at 11:14

viktarpunko

2 Answers2