MySQL Error 1093 - You can't specify target table

问题

执行如下sql要删除一些错误数据

delete from user_tag where tagId in (
    select ut.tagId from user_tag ut
    left join tag t on ut.tagId = t.tagId 
    where t.tagId is null 
)

结果

结果SQL错误，这是MySQL特有的一个现象

delete from user_tag where tagId in (
    select ut.tagId from user_tag ut
    left join tag t on ut.tagId = t.tagId 
    where t.tagId is null 
)
> 1093 - You can't specify target table 'user_tag' for update in FROM clause
> 时间: 0.001s

解决方案

修改为如下方式，delete 的

select count(1) from tag t ; -- 177
select count(1) from user_tag  ; -- 647

delete from user_tag where tagId in (
    select tagId from (select ut.tagId from user_tag ut
    left join tag t on ut.tagId = t.tagId 
    where t.tagId is null ) tt 
)
> Affected rows: 95
> 时间: 0.008s

结论

MySQL 不能在更新或删除的子语句中直接使用本表，可通过再套一层的方式绕过。