Update user email address

Question

Update user email address

Ed7

SSCrazy Eights

Points: 8519
More actions
August 31, 2009 at 2:32 pm

#138524

Hello Rooms,
Can anyone provide me best hints and solutions T-SQL scripts to update user email address?
UserInformation Table
SiteIDLoginNameLanguageIDLookAndFeelIDDefEmail
1User1.Name0 1 User1.Name@company1.com
2User2.Name0 1 User2.Name@company2.com
3User3.Name0 1 User3.Name@company1.com
4User4.Name0 1 User4.Name@company1.com
5User5.Name0 1 User5.Name@company3.com
I only wanted to update the DefEmail column value of “@company1.com” without change the “User2.Name”
For instance, I wanted to change from User2.Name@company2.com to User2.Name@NewCompany.com
Please help and advice.
Thanks in advance for big helps.
Edwin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Do me a favor - lookup REPLACE in Books Online and post what you have tried so far. I am more than willing to help you out, but I need to know that you have tried to solve the problem yourself, and what you have tried.

If you need help on how to post - review the first article I link to in my signature.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

rajdba Ten Centuries Points: 1362 More actions · Answer 2

Edwin,

Jeffrey wants to say to use Replace Function to achieve your goal as the below select statement

SELECT REPLACE (DefEmail,'@company1.com','@NewCompany.com')

FROM dbo.table_test

The same can be done for update statement and if the previous domain name is different(@company1.com,@company2.com) you can use CASE to achieve the same.

Thanks,

Raj

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Jeffrey Williams (8/31/2009)
Do me a favor - lookup REPLACE in Books Online and post what you have tried so far. I am more than willing to help you out, but I need to know that you have tried to solve the problem yourself, and what you have tried.

Thanks Raj for spelling out what Jeff was trying to get accomplished.

Yes, replace would work for this - and the syntax could be found via msdn, google, or BOL.

Edwin, have you tried this already? Have you tried something else already - as Jeff said, we can more easily help knowing just a little bit more information.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Williams (old account) SSC Rookie Points: 27 More actions · Answer 4

Jeff Williams (old account)

SSC Rookie

Points: 27

August 31, 2009 at 3:58 pm

#1047201

Hey Jason - again, no worries 😀

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 5

Hi Jeff,

I all ready tried some scripts in my test environments and run into some business issues.

Thanks for your hints and will try out your script.

-Edwin

Jeff Williams (old account) SSC Rookie Points: 27 More actions · Answer 6

Edwin - I really can't help without seeing what you have tried. I've pointed you in the right direction, but I am not sure where you are having problems. Seeing what you have tried would help me identify where to focus to help you learn.

extremenovice SSCrazy Points: 2118 More actions · Answer 7

Hi All, I'm a trainee dba and have been asked to do something very similar here whereby the email address in a table has to be changed from the "at" point @carlisleha.org.uk to @riverside.org.uk without modifying the beginning of the name in the email address i.e. johnsmith@carlisleha.org.uk.

So I give the REPLACE statement a go, and that doesn't save the changes in the table.

USE db name

SELECT REPLACE (EMAILADDRESS,'@carlisleha.org.uk','@riverside.org.uk')

FROM [dbo].[USERS]

Go

I then used the following 2 UPDATE statements, and they will not update anything.

UPDATE dbo.USERS

SET emailaddress ='@riverside.org.uk'

WHERE emailaddress ='@carlisleha.org.uk'

UPDATE dbo.USERS

SET emailaddress = replace(emailaddress, @carlisleha.org.uk, @riverside.org.uk

WHERE emailaddress like %@carlisleha.org.uk%

So i changed the Update to reference a column linked to that email address and it worked. However, this is not what i want it to do, there are almost 1000 records in the table.

UPDATE users

SET EMAILADDRESS ='sysadmin@carlisleha.org.uk'

WHERE Userno ='10'

Your help would be appreciated as right now I just can't see the obvious :-S

Thanks