July 12, 2006 at 12:07 pm
I need to go to the zip code table and pull the city and state if it's missing from the propertydetails table.
What am I missing?
update
propertydetails
set
p.sa_site_city = propertycenter.dbo.zipcodes.city,
p
.sa_site_zip = z.zipcodes.zip
from
propertyAK.dbo.propertydetails p join propertycenter.dbo.zipcodes z
on
z.zipcode = p.sa_site_zip
where
p.sa_site_zip is not null and isnull(p.sa_site_state,'')=''
and
p.sa_property_id = 86943
But I get the following error -
MSG 4104, Level 16, state1, line2
The Mulit-part identifier "sa_site_city" could not be bound
Any ideas?
July 12, 2006 at 12:13 pm
By aliasing the table in the FROM, but then updating the non-aliased tablename, you don't have correlation between the 2 objects:
-- Update the alias
update p
-- Don't use the alias to prefix the column(s) being updated
set
sa_site_city = z.city,
sa_site_zip = z.zip
from propertyAK.dbo.propertydetails As p
join propertycenter.dbo.zipcodes As z
on z.zipcode = p.sa_site_zip
where p.sa_site_zip is not null
and isnull(p.sa_site_state,'')=''
and p.sa_property_id = 86943
July 12, 2006 at 1:13 pm
This will also work, just drop off the alias in the set:
update propertydetails set
sa_site_city = z.city,
sa_site_zip = z.zip
from
propertyAK.dbo.propertydetails As p
join propertycenter.dbo.zipcodes As z
on z.zipcode = p.sa_site_zip
where
p.sa_site_zip is not null
and isnull(p.sa_site_state,'')=''
and p.sa_property_id = 86943
hth,
Lynn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply