How to remove duplicates on specific columns?

Question

I have a table like this:

// users
+----+----------+-------+---------------------+
| id |   name   |  age  | some other cols ..  |
+----+----------+-------+---------------------+
| 1  | Ali      | 15    | ..                  |
| 2  | John     | 15    | ..                  |
| 3  | Ali      | 22    | ..                  |
| 4  | Martin   | 18    | ..                  |
| 5  | Ali      | 15    | ..                  |
| 6  | John     | 30    | ..                  |
| 7  | John     | 15    | ..                  |
+----+----------+-------+---------------------+

I want to add a composite unique index on name, age columns. Currently, it gives me there are duplicates rows error. Already, I was using INSERT IGNORE .. to do this job but sadly IGNORE is no longer supported in MySQL.

Anyway, any idea how can I remove rows that have the same values on both name and age columns and keep just one of them (one of rows)? In other words, I want to remove all rows that have the same value on name and age columns except one of them.

So, here is the expected result:

// users
+----+----------+-------+---------------------+
| id |   name   |  age  | some other cols ..  |
+----+----------+-------+---------------------+
| 1  | Ali      | 15    | ..                  |
| 2  | John     | 15    | ..                  |
| 3  | Ali      | 22    | ..                  |
| 4  | Martin   | 18    | ..                  |
| 6  | John     | 30    | ..                  |
+----+----------+-------+---------------------+

Any idea how can I do that?

'IGNORE is no longer supported in MySQL.' - news to me what version are you on? — P.Salmon
– P.Salmon, Commented Jul 21, 2020 at 7:30

Ankit Jindal · Accepted Answer · 2020-07-21 07:35:43Z

3

Hope this works for you:

DELETE t1 
FROM users t1 
INNER JOIN users t2 
WHERE t1.id > t2.id AND t1.name = t2.name AND t1.age=t2.age;

answered Jul 21, 2020 at 7:35

Ankit Jindal

4,0963 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin AJ Over a year ago

In this query, will one row remain (from the ones that have duplicate)?

Ankit Jindal Over a year ago

@MartinAJ Yes, because of the first condition t1.id > t2.id. You can still create a clone of existing data just to be safe

Gordon Linoff · Accepted Answer · 2020-07-21 11:08:36Z

2

I would strongly suggest writing the code as:

delete u
    from users u join
         (select u2.name, u2.age, min(u2.id) as min_id
          from users u2
          group by u2.name, u2.age
          having count(*) > 1
         ) u2
         on u.name = u2.name and u.age = u2.age and u2.id > u.min_id;

The use of inner join with > is quite clever. However, I think that it might attempt to update the same row multiple times.

Consider this example:

id    name     age
 1    Doug      42
 2    Doug      42
 3    Doug      42
 4    Doug      42
 5    Doug      42

The inner join logic would attempt to delete row "5" four times. This version only deletes it once.

edited Jul 21, 2020 at 11:08

answered Jul 21, 2020 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Shafizadeh Over a year ago

. . . and u2.min_id > u.id;

CalleKhan Over a year ago

For me the ID check was correct as follows: u.uid > u2.min_id

Tim Biegeleisen · Accepted Answer · 2020-07-21 07:29:42Z

2

If you just want to view your data this way, then a basic select can do the trick:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT name, age, MIN(id) AS min_id
    FROM yourTable
    GROUP BY name, age
) t2
    ON t2.name = t1.name AND t2.age = t1.age AND t2.min_id = t1.id
ORDER BY
    t1.id;

In the case of two or more records which are "duplicate" with respect to having the same name and age values, the above query arbitrarily retains only the record having the smallest id value among them.

answered Jul 21, 2020 at 7:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

hussein zakizadeh · Accepted Answer · 2020-07-21 11:10:56Z

0

i created an example for your question:

 CREATE TABLE #myList (
  id int,
  [name] VARCHAR(100),
  age  int
);
  --drop table #myList

INSERT INTO #myList
  (id, [name], age  )
VALUES
  (1,'Ali',15),
  (2,'John',15),
  (3,'Ali',22),
  (4,'Martin',18),
  (5,'Ali',15),
  (6,'John',30),
  (7,'John',15)
  
 select id,[name],age from  ( SELECT 
     i.id,
    i.[name] [name],
    i.age age,
    RANK() OVER (PARTITION BY i.[name] ORDER BY i.id DESC) AS Rank1,
        RANK() OVER (PARTITION BY i.age ORDER BY i.id DESC) AS Rank2
FROM #myList AS i 

) select1 where  select1.Rank1 =1 or select1.Rank2 =1

result =

id  name    age
7   John    15
5   Ali     15
4   Martin  18
3   Ali     22
6   John    30

answered Jul 21, 2020 at 11:10

hussein zakizadeh

1731 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to remove duplicates on specific columns?

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related