update one table from another table

[wh0wants2know]

i have two tables, aci_zip and dbo_postalcodes. they both contain a bunch of zipcodes, but aci_zip has a field called ACIarea and dbo_postalcodes has a field called ACIZones. these two fields are the same. Now, most (but not all) of the zip codes in aci_zip are also in dbo_postalcodes and some of them have the ACIarea in aci_zip. i want dbo_postalcodes to get the ACIZones information from the ACIarea field in aci_zip where the columns city, state, and zip all correspond to the values in both databases. how would i write this update query, or is this even possible?

 

[PHV]

UPDATE dbo_postalcodes P
SET ACIZones = (SELECT ACIarea FROM aci_zip A WHERE A.city=P.city AND A.state=P.state AND A.zip=P.zip)
WHERE EXISTS (SELECT * FROM aci_zip A WHERE A.city=P.city AND A.state=P.state AND A.zip=P.zip)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wh0wants2know]

This looks like what i want to do, but as soon as i try to run this (i'm running it in access with aci_zip as a local table and dbo_postalcodes as a linked table to the sql server), it asks me for the values of A.city, P.city, etc. so it clearly is not taking these values from the tables that it's supposed to. is this just an access thing or is something wrong?

 

[PHV]

Ah, access question in the ANSI SQL forum ...
You may try this:
UPDATE dbo_postalcodes P INNER JOIN aci_zip A
ON A.city=P.city AND A.state=P.state AND A.zip=P.zip
SET ACIZones = ACIarea;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wh0wants2know]

that works. now, i'm only running this in access to test the query with my dev server and corresponding database. ultimately, i will need to run this on the sql server, probably via DBAMngr2k or similar, so will this query not work for that? will the access query work on the sql server or is it just that the sql query won't work under access?

 

[r937]

sql server update syntax for joined tables is different from access update syntax for joined tables

perhaps you could pursue this further in the appropriate forum for whichever database system you'd like the syntax for

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)