Complex Update Queries

[Jon]

I have two large databases, contacts (~300 records) and master (~2500
records). Both databases have a column SSN and rank amoung about 30
other columns. What I need to do copy the rank from master to contacts
where the SSN is the same.

UPDATE contacts
SET contacts.rank = master.rank
FROM contacts, master
WHERE contacts.SSN = master.SSN;

Now Access 2003 gives me errors say I cant have FROM in the query. I
am about to just copy and paste the 300 values manually since I cant
get this to work. Any guidance as to how or if such a query can be
done would be great. Thank you, Jon

PS I also tried this to no avail;
UPDATE contacts
SET rank = (SELECT master.rank FROM master WHERE master.SSN =
contacts.SSN);

 

[Douglas J Steele]

Try

UPDATE contacts INNER JOIN master
ON contacts.rank = master.rank
SET contacts.rank = master.rank

 

[Jon]

Thank you, that worked perfectly!
Jon