mysql同一张表查询并删除_Mysql删除同一张表的子查询语句

DELETE

FROM

TBL_NODE

WHERE

parent_node_id IN

(

SELECT

node_id

FROM

tbl_node

WHERE

parent_node_id =12345)

原来的语句如上，在mysql执行会报错如下！

[DELETE - 0 row(s), 0.000 secs]  [Error Code: 1093, SQL State: HY000]  You can't specify target table 'TBL_NODE' for update in FROM clause

Code: 1093 SQL State: HY000 --- You can't specify target table 'TBL_NODE' for update in FROM clause

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

mysql不支持这种删除。修改为如下即可：

DELETE

FROM

TBL_NODE

WHERE

parent_node_id IN

(

SELECT

*

FROM

(

SELECT

node_id

FROM

tbl_node

WHERE

parent_node_id = 12345 ) AS temp )