we need to update our mail addresses in a table, because we have a new domain-part. The local-part remains untouched In the table is a column for Name, Surname and Mail. (And other columns which are not important). We want it to look like this in the end: Name Surname Mail Test Name Test.Name@newdomain.com Test2 Name2 Test2.Name2@newdomain.com But while trying to do so we broke it and now the mail column only shows the new domain. We used the following code: update table set mail = Replace('olddomain.com','newdomain.com') where mail LIKE '%olddomain.com' So now we need to restore the mail column and add the new domain-part. Any help?

Reputation: 11

Update Column from another column in same table

we need to update our mail addresses in a table, because we have a new domain-part. The local-part remains untouched

In the table is a column for Name, Surname and Mail. (And other columns which are not important).

We want it to look like this in the end:

Name    Surname     Mail
Test    Name        [email protected]
Test2   Name2       [email protected]

But while trying to do so we broke it and now the mail column only shows the new domain. We used the following code:

update table
set mail = Replace('olddomain.com','newdomain.com')
where mail LIKE '%olddomain.com'

So now we need to restore the mail column and add the new domain-part. Any help?

Upvotes: 1

Reputation: 37473

replace() takes three arguments

update table
set mail = Replace(mail,'@olddomain.com','@newdomain.com')
where mail LIKE '%olddomain.com'

Upvotes: 1

Reputation: 1269953

I'm surprised this works. Normally, replace() takes three arguments:

set mail = Replace(mail, 'olddomain.com', 'newdomain.com')

I might suggest that you include the @ in the logic as well.

Upvotes: 2