How to delete duplicate rows in mysql with condition?-CodePudding

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

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

but actually result was :

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

Thanks in advance.

CodePudding user response：

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);

CodePudding user response：

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