"Operation Must use a updatable query" message

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mscertified - 03 Aug 2006 19:51 GMT

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.

Reply to this Message

E - 03 Aug 2006 20:51 GMT

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?

> Trying to run:
>
[quoted text clipped - 3 lines]
>
> Why this message? A.QuestionID is not defined in a relationship.

Reply to this Message

John Vinson - 03 Aug 2006 23:49 GMT

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

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]

Reply to this Message