这条sql语句错在什么地方？

Question

DELETE from li_make_code  where user_id in 
(    
    select user_id from li_make_code a 
        where 
            not exists (select 1 from li_user b  where b.id=a.user_id)
) ;

[Err] 1093 - You can't specify target table 'li_make_code' for update in FROM clause

这个语句应该怎么改合适

Answer 1

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

Answer 2

你是想把user_id在li_user表中找不到的给删了吧，为什么不直接用：

delete from li_make_code where user_id not in 
    (select distinct id from li_user)

Answer 3

你子查询和删除操作的是同一个表，不能同时查询和更新同一个表。
更改为join方式即可。

Answer 4

DELETE from li_make_code where user_id in ( SELECT user_id from
(select user_id from li_make_code a where not exists(select 1 from li_user b where b.id=a.user_id)) bb)