交表删除
mysql> delete from post_users where id in (
-> select u.id from post_users u
-> left join posts s on u.post_id = s.id
-> where s.id is null) ;
报错提示:
ERROR 1093 (HY000): You can't specify target table 'dz_users' for update in FROM clause
问题根源,不能直接这样,必须中间临时表。
解决办法:
delete from post_users where id in (
select id from (
select distinct u.id from post_users u
left join posts s on u.post_id = s.id
where s.id is null
) m
)