Updating two fields at once - 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

General SQL Server Forums

New to SQL Server Programming

Updating two fields at once

Author

Topic

bobbles22
Starting Member

18 Posts

Posted - 2010-08-20 : 06:07:18

Ok, I'm sure this is easy, but I'm a beginner here.

If I want to do two update queries in the same table, each of which work individually, is there a way to write them to a single query?

The queries below have the net effect of turning

phon_areacode=NULL
phon_number=01438123456

into

phon_areacode=01438
phon_number=123456

but I want to combine them to a single query.

They are:

update dbo.phone
set phon_areacode = '01438'
where phon_number like '01438%'

and

update dbo.phone
set phon_number = replace (phon_number, '01438', '')
where phon_number like '01438%'

This dovetails with another question I posted on here earlier about formatting phone numbers, but is still relevent to me to just how to amalgamate these two queries into one.

Many thanks again.

Bob

manojKumar
Starting Member

4 Posts

Posted - 2010-08-20 : 07:32:18

I think U can do it in this way..

update dbo.phone
set phon_areacode = '01438',phon_number = replace(phon_number, '01438', '')
where phon_number like '01438%'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-20 : 10:57:27

Your replace is potentially dangerous.

If it encounters this string for example:

'01438201438'

You'll end up with the string '2' which is not what you want

the query should probably look something like


UPDATE p SET
    [phon_areaCode] = '01438'
    , [phon_number] = RIGHT([phone_number], LEN([phone_Number]) - 5)
FROM
    dbo.phone AS p
WHERE
    p.[phon_number] LIKE '01438%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources