update row thats being selected from

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 row thats being selected from

Author

Topic

drpcken
Starting Member

20 Posts

Posted - 2008-07-07 : 21:45:46

I have a zipcode field in my table and I allow the following formats for zipcodes:

49583
49584-3949

well some of the current zip codes look like this:
495843949
it needs a dash (-) in between it. I wrote this query to do so, BUT i don't know how to make it apply to the current row its selecting from. here:

update contacts set zip = (select substring(zip, 1, 5)+'-'+substring(zip, 6, 4)from contacts where zip not like '%-%' 
and len(zip) >5) where  --then what?

I tried a subquery selecting the zip but its not working. Any help would be greatly appreciated.

Thanks!

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-07-07 : 22:15:41

update contacts
set zip = substring(zip, 1, 5)+'-'+substring(zip, 6, 4)
from contacts where zip not like '%-%'
and len(zip) >5

drpcken
Starting Member

20 Posts

Posted - 2008-07-07 : 22:38:46

quote:
Originally posted by dshelton

update contacts
set zip = substring(zip, 1, 5)+'-'+substring(zip, 6, 4)
from contacts where zip not like '%-%'
and len(zip) >5

lol once again i was thinking about it too hard.

Thank you !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources