I have a mysql table named "dev" with a column of "email"s. I want to select out all the unique e-mail addresses and delete the duplicates.
My structure is as follows:
id || email
1 [email protected]
2 [email protected]
3 [email protected]
What I want is...
id || email
1 [email protected]
2 [email protected]
What mysql query can I used to accomplish this?
Thanks in advance!
Create a new table and select the elements you want to keep into the new table. If you don't need to retain the original ids then you can do this:
INSERT INTO dev2 (email)
SELECT DISTINCT(email)
FROM dev
If you need to retain the id of the first row for each the email address then use this instead:
INSERT INTO dev2 (id, email)
SELECT MIN(id), email
FROM dev
GROUP BY email
Once you have filled the new table you can drop the original table then rename the new table to the correct name.