Jump to content
TULMER

updatetext

Recommended Posts

I need to update over 400 email address in a table in the database.  I need to keep the first half of the email address and change everything after the '@' symbol.

Example:  FROM:  john.doe@oldname.com  -- TO:  john.don@newcompanyname.com

Is there any easy way?

Share this post


Link to post
Share on other sites

If you're using MySQL, it has a replace() function https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace

It would look something like this:

UPDATE `table` SET `field` = REPLACE(`field`, 'oldname.com', 'newcompanyname.com')

Do not copy and paste that code because you might ruin your whole table. I haven't tested it to see if it does exactly what I expect it to, so you should run it on a test table and tweak the code if necessary.

If you're not using MySQL then you'll need to loop through all the records, process them in a server-side language and then update them.

Share this post


Link to post
Share on other sites

didn't work -- only replaces complete email address with the field name:

UPDATE    cicntp
SET             cnt_email = REPLACE ('cnt_email', 'oldname.com', 'newcompanyname.com')
WHERE     (cnt_email LIKE '%@oldname.com')

 

___ results are email address goes to "cnt_email" and nothing more.

Share this post


Link to post
Share on other sites

Don't put the field name in quotes, that's telling it to use the field's name as a string. Use backticks for field names. `cnt_email`

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×