CODEWITHSUNDEEP
mysqlsqlphpmyadminduplicates
Sol
Sol

Reputation: 770

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

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:

enter image description here Can you please explain I am very new to SQL! Thank you in advance!!

Upvotes: 1

Views: 6572

Answers (3)

skanecode
skanecode

Reputation: 63

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

Upvotes: 0

Jimish Gamit
Jimish Gamit

Reputation: 1024

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

Upvotes: 5

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

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

Upvotes: 1

Related Questions