Delete duplicate rows using MYSQL statement

Delete duplicate rows using DELETE JOIN statement

MySQL provides you with the DELETE JOIN statement that you can use to remove duplicate rows quickly.

The following statement deletes duplicate rows and keeps the highest id:

1 2 3 4 5

DELETE t1 FROM contacts t1         INNER JOIN     contacts t2 WHERE     t1.id < t2.id AND t1.email = t2.email;

The output is:

1	Query OK, 4 rows affected (0.10 sec)

It indicated that four rows have been deleted. You can execute the query that find duplicate emails again to verify the delete:

EXAMPLE :


DELETE t1 FROM

 marriage_time t1 

INNER JOIN 

marriage_time t2 

WHERE

t1.SRNO < t2.SRNO AND t1.EMAIL = t2.EMAIL