Delete duplicate rows using an intermediate table in MYSQL

The user table has 5 records with duplicate [email protected] email

Step 1. Create a new table whose structure is the same as the original table:

CREATE TABLE user_copy LIKE users

Step 2. Insert distinct rows from the original table to the new table:

INSERT INTO user_copy SELECT * FROM users GROUP BY email

Step 3. drop the original table and rename the immediate table to the original one

DROP TABLE users;
ALTER TABLE user_copy RENAME TO users;

Result