Update queries using more than one table

[Sn0wman]

I am an Access 2010 novice who is trying to update one table with information from another i.e. when table 1 col 1 = table 2 col 1, update table 1 col 2 with table 2 col 2, but keep getting syntax errors when using the following code. Can anyone help point out where I am going wrong.

UPDATE TrafficApr11toDate 
SET [Originating Country] = (SELECT OriginatingCountry.[Mapped Originating Country],
FROM OriginatingCountry
WHERE OriginatingCountry.[Intermediate Originating Country] = TrafficApr11toDate.[Originating Country (Original)]);

[jforbes]

I think this is what you are attempting to do

UPDATE TrafficApri11toDate 
LEFT JOIN OriginatingCountry 
ON TrafficApri11toDate.[Originating Country (Original)] = OriginatingCountry.[Intermediate Originating Country] 
SET TrafficApri11toDate.[Originating Country] = [OriginatingCountry].[Mapped Originating Country];

Access does things a little strange sometimes when using the Query Editor. It always makes me uncomfortable when there is no FROM statement.

[Sn0wman]

Thanks very much .....that's sorted it out although you did throw me for 10mins as one of the tables got renamed in your reply ;-).