Link to home
Start Free TrialLog in
Avatar of Rush_2112
Rush_2112

asked on

MySQL DB - Duplicate records

Hi all,

I have a MYSQL Db, there's a table with a field name of 'Email'.

How would I find out if there's any duplicates in that table, and if so, what they are?  I know querying DISTINCT will stop dups from showing, but I want to get rid of them, not just suppress them.

Thanks,
Avatar of oleggold
oleggold
Flag of United States of America image

select val1.val2,count(1) from table
group by  val1.val2,
having ,count(1) f>1
ASKER CERTIFIED SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In oracle I'd use rowid to clean it up,in mysql something similar
create view mydups as
select val1.val2,count(1) from table
group by  val1.val2,
having count(1) >1
then select d1.uniq_rowid,d2.uniq_rowidfrom mydups d1,mydups  d2
where d1.val1=d2.val1 and d1.val2=d2.val2
and d1.uniq_rowid>d2.uniq_rowid
other way create unique index on table diabled, enable it with expetion into exptions table , delete all occurences from exception table