How removing duplicate rows Group two columns?

Question

I have a table (babynames) in my database which has duplicate records that I want to delete.

Ex Records in My Table

id category_id  baby_name   url_role    
1      6        karthik     karthik    
2      7        kalaivanan  kalaivanan    
3      4        arun        arun    
4      6        vijayakumar vijayakumar    
5      6        karthik     karthik    
6      6        karthik     karthik    
7      4        karthik     karthik
9      6        vijayakumar vijayakumar    
8      4        karthik     karthik

I Need Result

id category_id  baby_name   url_role    
1      6        karthik     karthik    
2      7        kalaivanan  kalaivanan    
3      4        arun        arun    
4      6        vijayakumar vijayakumar    
7      4        karthik     karthik

I Need to remove Duplicate Records Found in Same Category with same baby_name. I don't want to create a new table with distinct entries for this. I Need to delete duplicate entries from the existing table without the creation of any new table.

DELETE FROM babynames 
    WHERE id NOT IN 
      (   SELECT MIN(id)  
            FROM babynames 
        GROUP BY baby_name,category_id
      )

Possible duplicate of [how to delete duplicate rows from a table in mysql](https://stackoverflow.com/questions/3271396/how-to-delete-duplicate-rows-from-a-table-in-mysql) — Nick, Feb 08 '19 at 03:36

score 0 · Answer 1 · answered Feb 08 '19 at 03:08

In MySQL, you should use a JOIN:

DELETE b
    FROM babynames b LEFT JOIN
         (SELECT baby_name, category_id, MIN(id) as min_id
          FROM babynames 
          GROUP BY baby_name,category_id
         ) bb
         ON bb.min_id = b.id
    WHERE bb.min_id IS NULL;

score 0 · Answer 2 · answered Feb 08 '19 at 03:40

You can use an EXISTS clause, taking care to wrap the internal FROM into a subquery to avoid the dreaded

You can't specify target table 'x' for update in FROM clause

error:

DELETE b
FROM babynames b
WHERE EXISTS (SELECT * 
              FROM (SELECT * FROM babynames) b1 
              WHERE b1.category_id = b.category_id
                AND b1.baby_name = b.baby_name
                AND b1.id < b.id)

Output:

id  category_id     baby_name       url_role
1   6               karthik         karthik
2   7               kalaivanan      kalaivanan
3   4               arun            arun
4   6               vijayakumar     vijayakumar
7   4               karthik         karthik

Demo on dbfiddle

score 0 · Answer 3 · answered Feb 08 '19 at 07:19

0

To delete duplicate records from table you can do like below

delete from names 
where id not in 
(
select min(id)
from names 
group by category_id,baby_name,url_role 

  )

answered Feb 08 '19 at 07:19

Ankit Agrawal

2,426
1
13
27

score 0 · Answer 4 · answered Feb 08 '19 at 09:17

Please Try this

 DELETE FROM babynames 
   WHERE id   IN 
                  (  
                  select id from (SELECT id ,  RANK() OVER (PARTITION BY baby_name,category_id
                 ORDER BY  sale DESC  )rw sales_rank FROM babynames)t1 where rw>1
                  )

How removing duplicate rows Group two columns?

4 Answers4