Update Table with Primary key from another table

[NZArchie]

Hey guys, I've had a wee search but I can't find code that seems to work in my situation. I'm trying to update my temp table with the Advisor ID from matching records in my advisor table. What's the problem with this?

UPDATE [TempTable-SovImport] SET [TempTable-SovImport].AdvisorID = (SELECT AdvisorID
FROM Advisors
WHERE (([Advisors]![FirstName]) = ([TempTable-SovImport]![AgentName])) AND (([Advisors]![LastName]) = ([TempTable-SovImport]![AdvisorLastName])));

 

[ByteMyzer]

Try:

UPDATE [TempTable-SovImport]
INNER JOIN Advisors ON [TempTable-SovImport].AgentName = Advisors.FirstName
                   AND [TempTable-SovImport].AdvisorLastName = Advisors.LastName
SET [TempTable-SovImport].AdvisorID = Advisors.AdvisorID;

 

[NZArchie]

Thank you!