How to create a correlated update subquery in MS-Access?

Question

I'm in the process of normalizing a few tables and I've added a surrogate primary key to a table called Exams which holds exam titles.

Previously, the child tables would just use the entire name of the exam as the FK entry.

Now that I've added a autonumbered field to the table, I want to update the entries that use it such as the table where the questions are from as there are over a thousand of them.

Going through each exam with a modified update once I find the name each time would take a while, so I decided to write a correlated subquery in a UPDATE query. The query looks like this:

UPDATE tblExamQuestion
SET ExamID = (SELECT ExamID FROM tblExam WHERE ExamName = tblExamQuestion.ExamName);

Unfortunately, once I write the subquery portion, Access refuses to give me the Run option and just displays the Design View, Datasheet View and SQL View for that query. However, it still displays the Update Query icon in the object explorer.

Is there a proper way to write this so that Access doesn't get upset?

Using: Access 2007 with a Access 2003 MDB database.

Answer 1

Why not:

UPDATE tblExamQuestion
INNER JOIN tblExam 
ON tblExam.ExamName = tblExamQuestion.ExamName
SET tblExamQuestion.ExamID = tblExam.ExamID