Delete duplicate values in a column using MySQL

You could have faced a situation where you have huge number of records and have to remove the duplicate entries (like duplicate email id) from it, if you have worked with data import. Of course you could do it programmatically, but you could do it faster using the Database itself.

Consider you have duplicate data in table 'Users' as below:
----------------------------
| ID    | Email            |
---------------------------- 
| 1     | user1@domain.com |
---------------------------- 
| 2     | user2@domain.com |
---------------------------- 
| 3     | user2@domain.com |
---------------------------- 
| 4     | user3@domain.com |
---------------------------- 

You have to create a new table with the same schema as the table 'Users'.

CREATE TABLE uniqueUsers LIKE Users;

Now you have a new table with the same schema as the table Users. This is the easiest method so that you do not have to create all fields and the constraints to the new table.

Now, we can copy the unique records to the new table.

INSERT INTO

uniqueUsers

(

ID,

Email

)

SELECT ID,Email

FROM Users 

GROUP BY Email

By using the Group By statement, you get the unique values for the field and those records get inserted into the new table uniqueUsers. So now you have the unique records in the table uniqueUsers.