phpmyadmin Question posted in
The official documentation can be found here.

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

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’

Tags:

2

Answers


  1. 0

    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 );
    Login or Signup to reply.
  2. 0

    This can be achieved in three steps.

    1. 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
    1. Remove or Rename the ‘job’ table
    DROP TABLE job
    ALTER TABLE job
RENAME job_archived
    1. 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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search