Solved

MySQL DB - Duplicate records

Posted on 2013-01-03
5
199 Views
Last Modified: 2013-01-04
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
Comment
Question by:Rush_2112
  • 5
5 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 38740455
select val1.val2,count(1) from table
group by  val1.val2,
having ,count(1) f>1
0
 
LVL 21

Accepted Solution

by:
oleggold earned 500 total points
ID: 38740459
mistype, heres:
[
select val1.val2,count(1) from table
group by  val1.val2,
having count(1) >1

Open in new window

0
 
LVL 21

Expert Comment

by:oleggold
ID: 38740472
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
 
LVL 21

Expert Comment

by:oleggold
ID: 38740489
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
 
LVL 21

Expert Comment

by:oleggold
ID: 38740490
other way create unique index on table diabled, enable it with expetion into exptions table , delete all occurences from exception table
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now