• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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,
0
Rush_2112
Asked:
Rush_2112
  • 5
1 Solution
 
oleggoldCommented:
select val1.val2,count(1) from table
group by  val1.val2,
having ,count(1) f>1
0
 
oleggoldCommented:
mistype, heres:
[
select val1.val2,count(1) from table
group by  val1.val2,
having count(1) >1

Open in new window

0
 
oleggoldCommented:
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
0
 
oleggoldCommented:
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
0
 
oleggoldCommented:
other way create unique index on table diabled, enable it with expetion into exptions table , delete all occurences from exception table
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now