update a table using a rows from another table 2

[artguy]

I have 2 tables. The first table (table1) is a list of zip codes and a distributor ID for each. The second table (table2) has a smaller list of zip codes. I need to change all of the distributor IDs in table1 to another ID number ONLY IF the zip code exists in the second table.

Then I need to do a query and find all of the zip codes in the table1 where, for example, distribID = 7 currently and change it to another number ONLY IF the zip code does NOT exist in table2.

I've attempted a search for this on Google and in this site with Google but I must be guessing the wrong wording.

Thank you for your time in reading this post. Any help/guidance would be appreciated.

bob

 

[PHV]

change all of the distributor IDs in table1 to another ID number ONLY IF the zip code exists in the second table
UPDATE table1 INNER JOIN table2 ON table1.[zip code]=table2.[zip code]
SET table1.[distributor ID] = AnotherID

find all of the zip codes in the table1 where, for example, distribID = 7 currently and change it to another number ONLY IF the zip code does NOT exist in table2
UPDATE table1
SET distribID = AnotherNumber
WHERE distribID=7 AND [zip code] NOT IN (SELECT [zip code] FROM table2)

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

 

[Remou]

Query A
1. Create a query that joins the zip codes in table 1 to the zip codes in table 2 by dragging the zip field from one to the other.
2. Examine the results of this query. If it returns the records you want, change it to an update query and use it to update the IDs.

Query B: See Find Unmatched Query.
1. Starting with a new query follow 1, above.
2. Right-click on the join line or choose View->Join Properties from the menu and select "include all records from table 1 and only those from table 2 where the join fields are equal".
3. Add zip code from table 2 to the design grid and type:
Is Null
On the criteria line.
4. Follow 2 above, choosing to update the zip as required.

 

[artguy]

Thank you both for your invaluable (and I must say FAST!!!) replies.

That's exactly what I needed. I'll give them a try! I kept trying INNER JOIN and just kept getting the WHERE portion wrong, I guess.