需要做的:一张表里,电话是唯一的,可是有很多重复的记录,现在要做的是 有重复记录的数据,电话号码 全部置为null
重复记录为:843条。
写了很多测试的如下:
1.
结果:失败,报错:You can't specify target table ‘’
错误提示就是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)
查了下百度和谷歌,说是Mysql的bug,有朋友建议等待mysql升级,哈哈。
2.
结果:失败,同样的错误
3.
结果:成功,但是效率超低,重复记录843条,执行了将近5分钟
4.
结果:成功,执行时间:9.91s
分析:
1、
<code>
select mobile from club_member a where a.mobile is not null
group by a.mobile having count(*) >1) b
</code>
最里层的子查询,查询出重复记录
2、
<code>
update club_member c set mobile=''
where exists (select 1 from (select mobile from club_member a where a.mobile is not null
group by a.mobile having count(*) >1) b where b.mobile= c.mobile);
</code>
where b.mobile=c.mobile 是与要修改的自关联表做比较相等
update club_member c set mobile='' 置为null
最后execute ok
oracle好像可以直接执行1。
Mark here!
重复记录为:843条。
写了很多测试的如下:
1.
update club_member a, (select mobile from club_member group by mobile having count(*) >1) b
set a.mobile='' where a.mobile = b.mobile;
结果:失败,报错:You can't specify target table ‘’
错误提示就是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)
查了下百度和谷歌,说是Mysql的bug,有朋友建议等待mysql升级,哈哈。
2.
update club_member c set mobile=''
where exists (select 1 from club_member a where a.mobile is not null
group by a.mobile having count(*) >1)
结果:失败,同样的错误
3.
update club_member a, (select mobile from club_member group by mobile having count(*) >1) b
set a.mobile='' where a.mobile = b.mobile;
结果:成功,但是效率超低,重复记录843条,执行了将近5分钟
4.
update club_member c set mobile=''
where exists (select 1 from (select mobile from club_member a where a.mobile is not null
group by a.mobile having count(*) >1) b where b.mobile= c.mobile);
结果:成功,执行时间:9.91s
分析:
1、
<code>
select mobile from club_member a where a.mobile is not null
group by a.mobile having count(*) >1) b
</code>
最里层的子查询,查询出重复记录
2、
<code>
update club_member c set mobile=''
where exists (select 1 from (select mobile from club_member a where a.mobile is not null
group by a.mobile having count(*) >1) b where b.mobile= c.mobile);
</code>
where b.mobile=c.mobile 是与要修改的自关联表做比较相等
update club_member c set mobile='' 置为null
最后execute ok
oracle好像可以直接执行1。
Mark here!