Mysql用同一张表查询的结果删除此表的数据报错

DELETE FROM study_name
WHERE name_id IN ( SELECT name_id FROM study_name WHERE name_id =20221209)

执行会报错如下:

[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 study_name

WHERE name_id IN (select * from(

SELECT

name_id

FROM study_name

WHERE name_id =20221209

) as temp

)