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

Question

Need to delete data from table_a. We have to delete data from table_a. So table_a join with table_b(It is done using subquery).

delete from table_a where id in (SELECT GROUP_CONCAT(N.id) FROM table_a N 
INNER JOIN table_b E ON N.form_id=E.form_id and N.element_id=E.element_id 
WHERE N.option_value=0 AND E.element_type IN('checkbox','radio','select')
AND N.id BETWEEN 13000 AND 14001);

When i exeute the above query, getting error is given below

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

How to fix this issue.

I have tried with the below query. is it ok?

delete from table_a where id in (SELECT * FROM(SELECT GROUP_CONCAT(N.id) FROM table_a N 
INNER JOIN table_b E ON N.form_id=E.form_id and N.element_id=E.element_id 
WHERE N.option_value=0 AND E.element_type IN('checkbox','radio','select')
AND N.id BETWEEN 13000 AND 14001)tblTmp);

Does this answer your question? [Delete statement in a same table](https://stackoverflow.com/questions/3346068/delete-statement-in-a-same-table) — GSerg, Sep 27 '21 at 07:03
*.. where id in (SELECT **GROUP_CONCAT(N.id)** FROM ..* - this will not work like you want - GROUP_CONCAT produces scalar CSV value, not a list of separate values. — Akina, Sep 27 '21 at 07:13

score 1 · Answer 1 · answered Sep 27 '21 at 07:12

DELETE t1
FROM table_a t1
JOIN ( SELECT N.id
       FROM table_a N 
       INNER JOIN table_b E ON N.form_id=E.form_id and N.element_id=E.element_id 
       WHERE N.option_value=0 
         AND E.element_type IN('checkbox','radio','select')
         AND N.id BETWEEN 13000 AND 14001 ) t2 USING (id);

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

1 Answers1