Removing Duplicates In MySql

Question

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?

ScaisEdge · Accepted Answer · 2016-06-09 21:40:17Z

1

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)

edited Jun 9, 2016 at 21:40

answered Jun 9, 2016 at 21:20

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JVMX Over a year ago

Tried: delete from my_table where (myFIELD, id) in (select myFIELD, max(id) FROM myTABLE GROUP BY myFIELD HAVING c > 1) But got error: Unknown column 'c' in 'having clause'

JVMX Over a year ago

Not Quite there yet! : delete from my_table where (myFIELD, id) in (select myFIELD, max(id) FROM myTABLE GROUP BY myFIELD HAVING count(*) > 1) ThrowsYou can't specify target table 'myTAble' for update in FROM clause

ScaisEdge Over a year ago

Which of the 3 query do you used ?

Collectives™ on Stack Overflow

Removing Duplicates In MySql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related