JVMX JVMX - 5 months ago 7
SQL Question

Removing Duplicates In MySql

I have inherited a database that has a "Duplicate problem".

when I run:

SELECT myFIELD, COUNT(*) c FROM myTABLE GROUP BY myFIELD HAVING c > 1;

I get ~600 records that are duplicated

None or tripled or any other multiple

I wish to kill off one of the records, leaving me with no duplicates.

What does the query look like?

Answer

You can use a query like this ... you should repete the query depending how many time the same row is duplicated ..

delete from my_table 
where (myFIELD, id) in  
(select a.myFIELD, max(a.id) 
FROM myTABLE as a  GROUP BY a.myFIELD HAVING count(*) > 1)

otherwise you can use

delete from my_table 
where (myFIELD, id) not in 
(select a.myFIELD, min(a.id) 
FROM myTABLE as a GROUP BY a.myFIELD )

this should delete all the duplicated rows in a shot

If there problem with table name

delete from my_table 
where (myFIELD, id) in  (select field, id from 
(select a.myFIELD as field, max(a.id) as  id
FROM myTABLE as a  GROUP BY a.myFIELD HAVING count(*) > 1) as t)