Question 112798 in Mysql Question posted in
The official MySQL documentation can be found here.

How to delete duplicate rows in mysql with condition?

I have a table like this:

Id  email     Active
---------------------
1    aaa        1      
2    aaa        1      
3    aaa        0      
4    aaa        0

I want to delete duplicate row but if Active have 1/0 value keep 1 value and delete 0 value.

I tried this query

select *  FROM tbl_name WHERE Id NOT IN (SELECT Id FROM tbl_name GROUP BY email)

And I expected this result :

Id  email  Active
---------------------
1    aaa        1

OR

Id  email  Active
---------------------
2    aaa        1

but actually result was :

Id  email  Active
---------------------    
4    aaa        0

Thanks in advance.

Tags:

2

Answers


  1. 0 
    DELETE t1
FROM test t1
JOIN test t2 ON (t1.active < t2.active OR (t1.active = t2.active AND t1.id > t2.id)) AND (t1.email = t2.email);
    Login or Signup to reply.
  2. 0

    Got to simulate your problem: http://sqlfiddle.com/#!9/67b0ed8/1

    CREATE TABLE test(
id smallint auto_increment not null,
email varchar(12),
active smallint,
  primary key test(id));
  
 insert into test
 values(null, 'aaa@aaa',1),
       (null, 'aaa@aaa',1),
       (null, 'aaa@aaa',0),
       (null, 'aaa@aaa',0),
       (null, 'bbb@bbb',1),
       (null, 'bbb@bbb',0);

    And splited to three parts

    01 – I updated all your records to inactive

    UPDATE test
SET active = 0 
where id in (select * 
               from (select id 
                     from test t
                     where t.active = 1 
                     and email in (SELECT   
                                   email
                                   FROM test
                                   GROUP BY email 
                                   having count(id) >=1)
                    )t);

    02 – I identified witch records are the same and activate them.

    UPDATE test
SET active = 1
WHERE id in (SELECT * FROM(
   SELECT max(id) from test 
   where active = 0
   group by email) t
);

    03- I deleted the rest of inactive records

    DELETE FROM test
WHERE active = 0;

    Let me know if it helped you

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

Back To Top
Search