MySql delete之You can't specify target table for update in FROM clause解决方法

记录MySql delete数据出现You can’t specify target table for update in FROM clause的解决方法。

踩坑MySql SQL语句:

1
delete from `group` where id in (select id from `group` where user_id not in (select id from `user`))

错误提示:

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

You can’t specify target table for update in FROM clause错误的意思是说,不能先select出同一表中的某些值,再同一个SQL语句中update这个表。

正确的MySql SQL语句:

1
delete from `group` where id in (select t.id from (select id from `group` where user_id not in (select id from `user`)) as t)

这样把select出来的结果通过临时表存储之后,You can’t specify target table for update in FROM clause这个问题就解决了。