How Can I Update Domains for Email Records In MySql

So I’m running the following query to change the domains for all emails in my dev database but I keep getting this error for duplicate records, I believe there is records where the text before @ sign is the same. Can you please advise how do I modify my exiting query so I don’t see this error?

UPDATE users SET email=REPLACE(email, SUBSTR(email,INSTR(email,'@')+1),'');

#1062 - Duplicate entry '[email protected]' for key 'email'



put the domainname to the username and add your domain. so any email is unique. you can easy split it wit SUBSTRING_INDEX like this.

        SUBSTRING_INDEX("[email protected]", "@", 1),'_',
        REPLACE(SUBSTRING_INDEX("[email protected]", "@", -1),'.','_'),
        '') as newemail;

| newemail                             |
| [email protected] |
1 row in set (0.05 sec)

UPDATE users
    SET email = 
        SUBSTRING_INDEX(email, "@", 1),'_',
        REPLACE(SUBSTRING_INDEX(email, "@", -1),'.','_'),