Friday, September 9, 2011

Mysql Delete duplicate rows

Mysql delete duplicate rows.


Way 1
DELETE t3 FROM (SELECT t1.name, t1.id FROM (SELECT name FROM TABLENAME GROUP BY name HAVING COUNT(name)>1) AS t0 INNER JOIN TABLENAME t1 ON t0.name = t1.name) AS t2 INNER JOIN TABLENAME t3 ON t3.name = t2.name WHERE t2.id < t3.id

Way 2
ALTER IGNORE TABLE TABLENAME ADD UNIQUE INDEX(COLUMNNAME);

No comments: