How to Remove Duplicate Entries from Table ?

[Faakhir]

There are certain entries in the database table that already exists. These entries are added more than once for some reason. How do I remove/delete these duplicate entries from the database table ?

[kelfro]

This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries :

Step 1: move the non duplicates (unique tuples) into a temporary table
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;

[Katty]

Before removing duplicate entries from the database table, first you need to find the duplicates(duplicate entries).

There is a handy query for finding duplicates in a table.
Suppose you want to find all email addresses in a table that exist more than once:
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

[Praetor]

Apart from using primary key of the table and putting queries using primary key to remove duplicate entries from table, there is another approach that you can use by creating a second table with a primary key or unique constraint on that column, and insert into the new table all the rows from the old table.
For each unique value, the first insert will succeed but all subsequents will fail due to violation of key or unique constraint.

Once you are done, you can rename the old table to something else, and rename the new table with the old name.