Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query

Author

Topic

gyana.ojha
Starting Member

5 Posts

Posted - 2010-07-19 : 09:21:50

Hello All Forum Member.. I Need a help ...My requirement is given below
I have a Email Field In my table ....

Suppose I have xyz@gmail.com, abc@yahoo.com and 123@hotmail.com

So My question is How Can I updated only domain name is a single update query.

For Example update xyz@gmail.com to xyz@AOL.com
update abc@yahoo.com to abc@rediffmail.com

Thanks and Regards
Gyana Ranjan Ojha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-19 : 09:26:48

replace(Your_Column,'@gmail.','@AOL.')

No, you're never too old to Yak'n'Roll if you're too young to die.

gyana.ojha
Starting Member

5 Posts

Posted - 2010-07-19 : 09:53:47

Hope You didnt get my queries. Above statement only update one record I want update all record by replace domain name

Thanks and Regards
Gyana Ranjan Ojha

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-19 : 13:00:19

This requirement can be completed in multiple ways. I will explain only two ways.

1. Using multiple case statement.

Declare @S table
(OldEmail varchar(500),
NewEmail varchar(500))

Insert into @S (OldEmail)
Select 'AAA@yahoo.com' union
Select 'AAA@hotmail.com' union
Select 'bbA@hotmail.com' union
Select 'bbA@Mymail.com'

select * from @s

Update @S set NewEmail =
Case
When CHARINDEX('@yahoo.', OldEmail)>0 Then replace(OldEmail,'@yahoo.','@AOL.')
When CHARINDEX('@hotmail.', OldEmail)>0 Then replace(OldEmail,'@hotmail.','@SQLMail.')
When CHARINDEX('@Mymail.', OldEmail)>0 Then replace(OldEmail,'@Mymail.','@Gmail.')
else
oldEmail
End

select * from @s

You can use this method when the domain names to be replaced are few.

2. Using a temporary table having old domain and new domain.

--Data table having email values
Declare @S table
(OldEmail varchar(500),
NewEmail varchar(500))

--Table having domain names
Declare @Domain table
(Old varchar(500),
New varchar(500))

Insert into @S (OldEmail)
Select 'AAA@yahoo.com' union
Select 'AAA@hotmail.com' union
Select 'bbA@hotmail.com' union
Select 'bbA@Mymail.com'

Insert into @Domain (Old,New)
Select '@yahoo.','@AOL.' union
Select '@hotmail.','@SQLMail.' union
Select '@Mymail.','@Gmail.'

Update @S Set NewEmail = replace(OldEmail, d.Old, d.new)
from @S s , @Domain d
where s.OldEmail like '%'+d.Old+'%'

Select * from @s

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources