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]
