It’s not unheard of for a company to change e-mail domain in mid-thrust; maybe it’s been bought out, or rebranded, or the parent company has spun it off to its own brand.
Only you’ve got hundreds of employees, each one with their own email address, and your MySQL database is in dire need of updating to reflect this.
To get around this, you’ll need to replace the relevant part of each email string within an update statement, grabbing the hostname substring (after the ‘@’) with a REPLACE, and replacing it.
UPDATE table SET email=REPLACE(email,'OLDHOST.com', 'newhost.com');
Note: REPLACE() is case-sensitive, so if needs be, you can use LOWER(email) inside the REPLACE function if you need to catch all case possibilities, as below:
UPDATE table SET email=REPLACE(LOWER(email),'oldhost.com', 'newhost.com');
This will also convert all your email addresses to lowercase, so be aware of that.
One thought on “MySQL: Mass email change”
Do you write your own article or not, if yes you do know what you’re doing, keep on the great job, oh and you might write some PDFs.