CODEWITHSUNDEEP
mysqlduplicates
Eve
Eve

Reputation: 13

MySQL remove all but one value based on primary key and a column

My Table

 id   userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 3  |  123    |  [email protected]
 4  |  123    |  [email protected]
 5  |  123    |  [email protected]
 6  |  123    |  [email protected]

Result Desired

id    userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 3  |  123    |  [email protected]
 4  |  123    |  [email protected]
 5  |  123    |  [email protected]

But I am getting

id    userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 6  |  123    |  [email protected]

I tried (I am new to SQL)

DELETE FROM table 
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id) 
FROM table n GROUP BY n.emailid) x)

Upvotes: 0

Views: 209

Answers (1)

Ullas
Ullas

Reputation: 11556

Try This.

QUERY

DELETE FROM table_name 
WHERE id NOT IN
(
   SELECT MIN(id)
   FROM table_name
   GROUP BY userid,emailid
);

Upvotes: 1

Related Questions