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:[EMAIL PROTECTED] Sent: 20 November 2008 00:35 To: jean claude babin Cc: mysql@lists.mysql.com 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/[EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
