SQL How to delete duplicate rows of a specific column and keep one

Question

The following code returns a list of duplicate rows of a specific column. In this case I am trying to find duplicates of university name. And my problem is how could I delete the duplicates and leave just one copy for each different university_name?

    Select * from `university` where `university_name` in ( select `university_name` from `university` group by `university_name` having count(*) > 1 )

This is the result:

Can you please explain I am very new to SQL! Thank you in advance!!

if you don't care which of the two you want to delete, group the entire result and select their IDs. Then delete the result. You could select the MAX(id) if you want to delete the second one. — ericosg
– ericosg, Commented Feb 12, 2016 at 7:56
If there are duplicates for a university_name, which row do you want to keep? — jarlh
– jarlh, Commented Feb 12, 2016 at 8:09
actually I might want to greater id to be deleted and keep the lowest id for future reference — Sol
– Sol, Commented Feb 12, 2016 at 8:17

Jimish Gamit · Accepted Answer · 2016-02-12 09:23:02Z

5

1) If you want to keep the row with the lowest id value:

DELETE a
FROM university a, university b
WHERE a.id > b.id
AND  b.university_name=a.university_name

2) If you want to keep the row with the highest id value:

DELETE a
FROM university a, university b
WHERE a.id < b.id
AND  b.university_name=a.university_name

edited Feb 12, 2016 at 9:23

answered Feb 12, 2016 at 8:02

Jimish Gamit

1,0345 silver badges15 bronze badges

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

1 Comment

Sol Over a year ago

I was not able to check if this works but thank you for the help

Rahul Tripathi · Accepted Answer · 2016-02-12 07:57:42Z

1

Try this:

DELETE  u
FROM    university u
        LEFT JOIN
        (
            SELECT MIN(ID) ID, university_name
            FROM    university
            GROUP   BY university_name
        ) v ON  u.ID = v.ID AND
                u.university_name = v.university_name
WHERE   v.ID IS NULL

answered Feb 12, 2016 at 7:57

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Comments

skanecode · Accepted Answer · 2017-05-24 23:46:36Z

0

Delete the duplicates and keep the row with oldest id

DELETE a
FROM university a
JOIN university b ON a.university_name = b.university_name
WHERE a.id > b.id

answered May 24, 2017 at 23:46

skanecode

637 bronze badges

Collectives™ on Stack Overflow

SQL How to delete duplicate rows of a specific column and keep one

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related