mysql 删除重复数据问题 （you can't specify target table……）

 

mysql中对删除重复数据的sql语句支持有一定问题  不能像SQLServer一样根据同一表查询后的结果删除

像下面这句SQL就不能执行（忽略聚合的约束）Mysql对聚合的约束没那么严格

delete from distinctss where id in (select id from distinctss group by name1,name2 having count(*)>1)

那么用mysql的同志可以换其他方法了，比如：可以修改表中的特殊字段，然后根据字段再删出

update tablename set xxx=” “  where ………………
delete form tablename where xxx=”“

或者新建临时表

create table tmp as select id from distinctss group by name1,name2 having count(*)>1;
delete from distinctss where id not in (select col1 from tmp); 
drop table tmp;

 

转载于:https://www.cnblogs.com/Shalove/p/3194741.html