List:General Discussion« Previous MessageNext Message »
From:roger.maynard Date:November 20 2008 8:11am
Subject:RE: How to remove the duplicate values in my table!
View as plain text  
I have always used this for de-duplicating...

ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( myField1, myField1 ) ;

It works a treat, hope it helps

Roger


-----Original Message-----
From: Brent Baisley [mailto:brenttech@stripped] 
Sent: 20 November 2008 00:35
To: jean claude babin
Cc: mysql@stripped
Subject: Re: How to remove the duplicate values in my table!


On Nov 19, 2008, at 3:24 AM, jean claude babin wrote:

> Hi,
>
> I found the bug in my servlet ,when I run my application it enter  
> one record
> to the database without duplicate values.Now I want to clean my  
> table by
> removing all duplicate rows .Any thoughts?

I assume you have a unique record identifier like and auto_increment  
field? If you not, add and auto_increment field, you have to have a  
unique ID.

Assuming the "deviceId" field is what indicates a duplicate:
SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY  
deviceId HAVING c>1

That will give you the highest unique Id of each duplicate, which is  
what you want to delete assuming you want to keep the first record. If  
you want to keep the latest, change it to min.

Then you want to join on that select so you can use it as your delete  
filter.
DELETE table FROM table JOIN (
SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY  
deviceId HAVING c>1
) as dupSet ON dupSet.maxUid=table.uniqueId

That will delete one duplicate record for each duplicate group at a  
time. So if you have 10 of the same duplicate, you need to run the  
query 9 times. It wouldn't be too hard to add another subquery (i.e.  
LEFT JOIN on the dup select WHERE table.uniqueId IS NULL) to that to  
filter so you can delete all duplicates in 1 shot. This has always  
been something I had to do very infrequently, so I never bothered  
taking it further.

Hope that help!

Brent Baisley

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.175 / Virus Database: 270.9.2/1782 - Release Date:
11/19/2008 6:55 PM
Thread
How to remove the duplicate values in my table!jean claude babin19 Nov
  • Re: How to remove the duplicate values in my table!Brent Baisley20 Nov
    • RE: How to remove the duplicate values in my table!roger.maynard20 Nov