Pad out field values

I am attempting to import a MYSQL database member data table into a CMS (Expression Engine) and have come across a few problems, one of which is my table has the same values for some of the fields and Expression Engine doesn’t like this.

So ideally I would like to create a new table that will not contain any such duplicates. As I will have the original table still I am not too concerned about the fact some members will not be copied to the new table.

I am working with this data in phpMyAdmin so hopefully there is some magic I can perform there to deal with this.

The 2 fields that have duplicate values are

email
screen_name

Please note that some records may have just a duplicate email, some may have a duplicate screen_name and some may have both.

I tried the following but it seems to have left duplicates in the new table still



CREATE TABLE memberNew as
SELECT * FROM member WHERE 1 GROUP BY email, screen_name


Any help appreciated

p.s Sorry subject is incorrect, I was going to post about another issue but then changed to this issue.

Ah great - thanks Rudy. I will give that a go.

create your memberNew table not with CREATE SELECT but with explicit column definitions

then add a UNIQUE index on the email, and another UNIQUE index on the screen name

then do INSERT IGNORE with SELECT