Simple Update??? - 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 2000 Forums

Transact-SQL (2000)

Simple Update???

Author

Topic

kstewart
Starting Member

2 Posts

Posted - 2008-06-20 : 23:02:38

Hi all,

I need to do an update, that seemed simple to start with, but I have lost my way......

I have one table:

StreetName StreetID
Street1 Road 45678
Street1 Road 0
Street1 Road 0
Street2 Road 45679
Street2 Road 0
Street2 Road 0

I need to update the StreetID field for all records that have a value of 0 to a real value. For example The 2 records for Street1 Road with a value of 0 need to be updated to 45678, and the same for Street2 Road.

Anyone know a simple script to do this?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 23:13:31

Any one of these

1
Update t1
set t1.streetid=t2.streetid
from your_table t1 inner join (select streetname,max(streetid) as streetid from your_table group by streetname) as t2
on t1.streetnamet2.streetname

2
update t
set streetid=(select max(streetid) from your_table where streetname=t.streetname)
from your_table t

Madhivanan

Failing to plan is Planning to fail

kstewart
Starting Member

2 Posts

Posted - 2008-06-20 : 23:22:15

So easy when you know how....
Thanks, greatly apreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources