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),'test.com');

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

Thanks

Answer

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.

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

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

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