Subquery

[Ruszaj]

I am trying to update a table with data in a "look up table." Specifically, I
have a table that contains zip codes but is missing both city & state fields.
The zip codes are 9 digits (5 digit zip code followed by 4 zeros) in one table
& 5 digits in another. I cannot seem to join the 2 tables because of the
discrepency in values. I've tried regexp with no luck.
Any suggestions would be greatly aprreciated.

[Dan Bracuk]

The syntax is database specific, but you want to do something like this:

update t1
set city = t2.city, state = t2.state
from t1 join t2 on zipcode = substr(t2.zipcode, 1, 5)
where city is null

Originally posted by: Ruszaj
I am trying to update a table with data in a "look up table." Specifically,
I have a table that contains zip codes but is missing both city & state fields.
The zip codes are 9 digits (5 digit zip code followed by 4 zeros) in one table
& 5 digits in another. I cannot seem to join the 2 tables because of the
discrepency in values. I've tried regexp with no luck.
Any suggestions would be greatly aprreciated.

[Ruszaj]

Thanks!:D