May 13, 2009 at 9:58 AM
To remove duplicate rows from the table,you can use either of the two approaches in the script. Here we are going to remove rows have same id i.e 1.
CREATE TABLE backup_table AS
SELECT * FROM users WHERE 1 GROUP BY id ;
DROP TABLE users;
RENAME TABLE backup_table TO users;
ALTER IGNORE TABLE users ADD UNIQUE INDEX(id);
In the First approach, we have written three manual steps to create the new backup table with unique entries then we will remove the older table and at last we will rename the backup table with the same name of the older table that we have deleted. Here users is older table name.
In Second Approach, we have added unique index for id.