How can I delete duplicate (mixture of two rows) rows in SQL?

Question

I have a table like this:

id   c1   c2
1    5    abc
2    5    abc
3    2    xyz
4    2    xyz
5    68   sdf

I want to remove rows where c1 & c2 are same (that is row with id 2 and 4)

Edit: I tried

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (c1, c2);

but got this

1834 - Cannot delete rows from table which is parent in a foreign key constraint 'xyz' of table 'abc'

Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Sami Kuhmonen, Mar 12 '19 at 05:22
A good solution can be found [here](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server). — yuan cheng, Mar 12 '19 at 05:25
There are several solutions in the duplicate, not just one. Tried the others? — Sami Kuhmonen, Mar 12 '19 at 05:26

score 0 · Accepted Answer · answered Mar 12 '19 at 05:32

i suppose the table name is foobar , and c1 and c2 is not nullable .

this query will select the duplicate

select d.* from ( select c1 ,c2 , count(*) as cnt , min(id) as mid from foobar         group by c1,c1 ) as e 
       join foobar d on d.c1=e.c1 and d.c2=e.c2 and d.id > e.mid ;

You must create a temporary table with the list of all id you want to delete .

create table bad_id_foobar as select d.id  from ( select c1 ,c2 , count(*) as cnt , min(id) as mid from foobar         group by c1,c1 ) as e 
       join foobar d on d.c1=e.c1 and d.c2=e.c2 and d.id > e.mid ;

this query will delete the duplicate

 delete from foobar           where id in ( select b.id from  bad_id_foobar b );

score 0 · Answer 2 · answered Mar 12 '19 at 05:40

This can be achieved in three steps.

Insert unique records into a tmp table (same table structure as 'jobs').

INSERT INTO tmp (`c1`, `c2`) 
    SELECT c1, c2 
    FROM jobs 
    GROUP BY c1, c2

Remove or Rename the 'job' table

DROP TABLE job

ALTER TABLE job
RENAME job_archived

Rename 'tmp' table to 'job'

ALTER TABLE tmp
RENAME job

This how I do a task like this. There may be a better way to do the same... Cheers!

How can I delete duplicate (mixture of two rows) rows in SQL?

2 Answers2