php - What is wrong with this sql statement?

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

How should this statement be changed appropriately?

Answer 1

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

Answer 2

You want to delete the user_id that cannot be found in the li_user table? Why not just use:

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

Answer 3

Your subquery and delete operations are on the same table. You cannot query and update the same table at the same time.
Just change to join mode.

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)