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.
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 StreetIDStreet1 Road 45678Street1 Road 0Street1 Road 0Street2 Road 45679Street2 Road 0Street2 Road 0I 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 these1Update t1set t1.streetid=t2.streetid from your_table t1 inner join (select streetname,max(streetid) as streetid from your_table group by streetname) as t2on t1.streetnamet2.streetname2update t set streetid=(select max(streetid) from your_table where streetname=t.streetname)from your_table tMadhivananFailing 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. |
 |
|
|
|
|