CODEWITHSUNDEEP
mysqlsql
lqhcpsgbl
lqhcpsgbl

Reputation: 3782

MySQL delete with where not in error

I have a table like this:

create table person (
  id integer,
  Email varchar(100)
);


insert into person values (1, '[email protected]');
insert into person values (2, '[email protected]');
insert into person values (3, '[email protected]');
insert into person values (4, '[email protected]');

Now , I need delete duplicate lines keep the min Id which means after delete the table will be:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
+----+------------------+

I got this to get a list non-duplicate id:

select Id from Person group by Email;

And delete code is:

delete from Person
  where Person.Id not in 
    (select Id from Person group by Email);

But not works for me, why I cannot write like this and how to fix it?

Upvotes: 0

Views: 457

Answers (4)

ahmet
ahmet

Reputation: 61

I agree with Russ.

First you should go edit>preferences>sql editor and uncheck the safe update box. Then select the query>reconnect to server. Finally run the following query.

delete from Person
  where Person.Id not in 
 (select id from (select MIN(Id) as id from person group by email) as p)

Upvotes: 0

fthiella
fthiella

Reputation: 49049

This query will delete all duplicated rows, and keep the one with the lowest ID:

DELETE p1.*
FROM
  person p1 INNER JOIN person p2
  ON p1.Email=p2.Email
     AND p1.id>p2.id

Please see it working here.

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can easily do it with JOIN as

delete p from person p 
join ( 
  select min(id) as id ,email from person group by email 
)p1 
on p1.id < p.id and p1.Email = p.Email ;

Upvotes: 1

Russ
Russ

Reputation: 4163

I believe this will give you what you are looking for:

delete from Person
  where Person.Id not in 
    (select MIN(Id) from Person group by Email);

Upvotes: 0

Related Questions