Using phpmyadmin to manage duplicates

Question

Hi! I'm looking for a tool that allows me to easily manage duplicate entries in the MySQL database.

In a sense, I don't want to make my columns 'UNIQUE', but I want to review the records that happen to have exactly the same value in a column.

It would be nice if I could craft such an SQL query that shows only such records using the usual interface.

Answer 1

Should be possible to select them with a query:

select myColumn, count(myColumn) as myCount from myTable group by myColumn having myCount > 1 order by myCount; 

Answer 2

I'm not sure about the interface, but this tool certainly does the former: http://www.dodownload.com/business+manage/access+database/mysql+delete+remove+duplicate+entries+software.html

Answer 3

To get duplicates, just use a self-join on the table :

select t1.id, t2.id, t1.value
from table t1
inner join table t2 on t1.value=t2.value
where t1.id < t2.id

The t1.id < t2.id will make sure every duplicate will only appear once.