"Operation Must use a updatable query" message

M

mscertified

Trying to run:

UPDATE tblActivityQuestions AS A SET A.QuestionID = (SELECT Q.ID FROM
tblQuestions As Q WHERE A.ChecklistID = Q.ChecklistID AND A.Sequence =
Q.Sequence);

Why this message? A.QuestionID is not defined in a relationship.
 
E

E

Would the A.CheckListID be the issue because the alias 'A' is not defined
within the Select query?

In other words, this line: WHERE A.ChecklistID
Does it scope to the alias in Update?
 
J

John Vinson

Why this message? A.QuestionID is not defined in a relationship.
Click to expand...

Try using a Join rather than a subquery:

UPDATE tblActivityQuestions AS A INNER JOIN tblQuestions AS Q
ON A.ChecklistID = Q.ChecklistID
AND A.Sequense = Q.Sequence
SET A.QuestionID = Q.ID;

You will need a unique Index on the combination of ChecklistID and
Sequence.

John W. Vinson[MVP]
 
Top