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

1
2
3
4
5
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特有的一个现象

1
2
3
4
5
6
7
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 的

1
2
3
4
5
6
7
8
9
10
11
12

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