operation must use an updatable query

[anniebai]

Can someone help me with this query, which gives the error message "operation must use an updatable query"? Thanks a lot.

UPDATE publications set publications.pmcid = (select fname.pmcid from fname where fname.pmid=publications.pubmedid)

this following query works fine:

UPDATE publications inner join fname on fname.pmid=publications.pubmedid
set publications.pmcid = fname.pmcid

[NeoPa]

Annie, I see you are a fan of subqueries.

I think you may have stumbled upon this problem before though. Have another look through one of your earlier threads for more info on this (Does Subquery nested in Update query work?).

PS. Please remember to use the [ CODE ] tags when posting code. It saves us the trouble of having to add them for you.

[anniebai]

Annie, I see you are a fan of subqueries.

I think you may have stumbled upon this problem before though. Have another look through one of your earlier threads for more info on this (Does Subquery nested in Update query work?).

PS. Please remember to use the [ CODE ] tags when posting code. It saves us the trouble of having to add them for you.

hmm. I thought I had the same problem before. thanks for reminding me :-)
I know I can work around to achieve the same goal, but I really want to figure out whether this is because Access doesn't support this query or the query itself has a problem. The error message Access gives is misleading.

[NeoPa]

hmm. I thought I had the same problem before. thanks for reminding me :-)
I know I can work around to achieve the same goal, but I really want to figure out whether this is because Access doesn't support this query or the query itself has a problem. The error message Access gives is misleading.

I'm not sure why you would consider this error message misleading. It seems to me to be wholly appropriate.

A more detailed and full answer to your question though, can be found by following the link Stewart provided in post #2 of the linked thread.

[anniebai]

Maybe i missed something, but I've read the troubleshoot page, as far as I understand, none of the items listed answers my question.

[NeoPa]

Let me have a dig and see if I can find a clear explanation for you Annie...

[NeoPa]

I must admit that, from my recollection, I thought there was a section indicating that subqueries couldn't be used within an updatable query.

This seems not to be the case, so apologies for assuming that answered your question.

It is of course, still possible for there to be such a limitation, which is simply not documented in the article :(

Later I will look at setting up a quick test-rig and seeing if I can show that to be the case, as well as finding some alternative SQL for you to use in this case if I can.

[anniebai]

Thank you NeoPa.
I am sure the alternative SQL will be useful for people like me, I look forward to seeing them.

[NeoPa]

I'm not sure how interesting this will prove Annie. I find that the alternative SQL I would use is in fact the same (laid out differently) as the second set from your first post.

UPDATE [Publications]
SET [PMCID]=(SELECT [PMCID]
             FROM [FName]
             WHERE FName.PMID=Publications.PubMedID)

UPDATE [Publications] INNER JOIN [FName]
    ON FName.PMID=Publications.PubMedID
SET Publications.PMCID=FName.PMCID

[Stewart Ross]

Anniebai, Access simply cannot do this kind of update using a subquery in the way listed. Regardless of the reason why, it cannot do it (I have checked for myself to make sure, using a number of different subqueries with no success).

The alternative SQL using a very straightforward inner join works perfectly, which is what you mentioned in post 1 (and this in turn followed on from the SQL NeoPa had provided in the final post of the previous thread).

I'm not sure that there is more we can do here; in posting the link to the Microsoft Knowledgebase article in the previous thread I was trying to advise you that there can be many reasons for queries being non-updatable in Access, not all of them obvious. The article was indicative of the reasons but not exhaustive.

If using standard joins works and subqueries doesn't, then don't use the subquery approach to updates in Access, regardless of the success of such approaches in SQL Server or any other DB.

-Stewart

[NeoPa]

I have now set up a rig where I do something very similar to your update. In the standard way it works fine, but it complains about updatable recordsets when I tried with the subquery version.

I think you can take it that this is not supported in Jet SQL, even though the knowledgebase article didn't mention it.