MySQL command Help – How to Remove Duplicate Data or Rows

How to remove duplicate data or rows from a MySQL data table by using a single command or a query?

If the output of a MySQL query contains duplicate data or rows, you can eliminate or remove those by using the DISTINCT keyword before the selected column name.

Mysql>

SELECT firstname FROM address;

Then you need unique firstname (remove duplicate firstnames):

Mysql>

SELECT DISTINCT firstname FROM address;

DISTINCT can be used with multiple columns also:

Mysql>

SELECT DISTINCT firstname,city FROM address;

>>Next thing is, if you want to permanent delete the duplicate data, you can use:

Mysql>>

DELETE FROM table1
USING table1,table1 AS vtable
WHERE (NOT table1.id=vtable.id)
AND (table1.field_name=vtable.filed_name);

This code tells mysql that there is a table called ‘table1’, then it tells that we use ‘table1’ and a new virtual table ‘vtable’ with the values of table1. Then 3^rd row tells mysql to not to compare records itself. Then finaly we command there should not be records with the same field_name.