mysql查找数据表中重复记录并更新

查找重复：select test,count(*) as count from table group by test having count>1;

更新重复：update table set test=1 where rid in(SELECT test FROM table GROUP BY test HAVING COUNT( * ) >1)//这样是不行的 会报错You can’t specify target table

只能间接创建临时表的方式，执行后，删除掉。

create table tmp as SELECT test FROM table GROUP BY test HAVING COUNT( * ) >1;
update table set test=1 where test in( SELECT test FROM tmp)
drop table tmp;