Mysql: How to delete all duplicates that violate UNIQUE constraint

Question

I want to add a UNIQUE index to a table, like this:

ALTER TABLE `mytable` ADD UNIQUE `myunique_name`(`first`, `second`, `third`);

Mysql responds with:

Duplicate entry '1-2-3' for key 'myunique_name'

I know for sure that this combination is just one out of thousands that violate the constraint.

In this special case I know for sure that all the rows that contain the same values in the three specified columns also contain the same data in the other relevant fields (the primary index differs of course, but is irrelevant), therefore all the duplicates can be deleted.

Is there a way to do delete all duplicate entries but keep one (doesn't matter which primary key is kept) so that the unique index can be added?

Could you please send your table schema with some test data in order to elaborate your question. — Syed Muhammad Mubashir, May 21 '18 at 09:55
Possible duplicate of [How to remove mySQL duplicate entries](https://stackoverflow.com/questions/11621314/how-to-remove-mysql-duplicate-entries) — Progman, May 21 '18 at 10:43
Might also be helpful: https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows?rq=1 — Progman, May 21 '18 at 10:43

Syed Muhammad Mubashir · Answer 1 · 2018-05-21T11:23:39.430

CREATE TEMPORARY TABLE IF NOT EXISTS MyTable engine=memory 
select 1 as id, 1 col1,1 col2,1 col3
union all
select 2 as id, 2 col1,2 col2,2 col3
union all
select 3 as id, 3 col1,3 col2,3 col3
union all
select 4 as id, 4 col1,4 col2,4 col3
union all
select 5 as id, 1 col1,1 col2,1 col3
union all
select 6 as id, 2 col1,2 col2,2 col3


CREATE TEMPORARY TABLE IF NOT EXISTS MyDuplicateTableWithCount engine=memory 
select col1  , col2  , col3, count(*) Count_1
from MyTable
group by col1  , col2  , col3
having count(*)>1


  select a.* from  MyTable a
    inner join 
    (select col1  , col2  , col3
    from MyDuplicateTableWithCount
     ) b
     on a.col1 =b.col1 and a.col2 =b.col2 and  a.col3 =b.col3
    order by a.id

After getting the duplicate id's write your delete query specifyinging duplicate id's as

delete from myTable where id in (5,6)

Also use below query using myTable from above

CREATE TEMPORARY TABLE IF NOT EXISTS MyTable2 engine=memory 
 SELECT MIN(id) as id, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3

DELETE a FROM MyTable as a
LEFT  JOIN (
   SELECT * from MyTable2
) as b ON
   b.id = a.id
WHERE
   b.id IS NULL

Mysql: How to delete all duplicates that violate UNIQUE constraint

1 Answers1