Bulk update in MYSQL

Question

I have a table containing data about some users.

Many of them use our in house email system at OLDHOST.com. We have updated to a newer system at NEWHOST.com. All the users usernames are the same, so if you had [email protected], you are now [email protected]

Is there a better way to change all the email fields in the users table without selecting all the rows in say PHP, then checking if the email has OLDHOST in it, then replacing the string to NEWHOST?

Is there a baked in awesome SQL statement to help with this?

---

Example of some of the table (simplified)

id | firstname | surname | email
------------------------------------------------
1  | dave      | smith   | [email protected]
2  | barry     | jones   | [email protected]

etc.

All that needs to be changed is any emails that contain OLDHOST (Not all do) to NEWHOST.

Answer 1

You'll need to replace the relevant part of each 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 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 :)