By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

operation must use an updatable query

P: 51
Can someone help me with this query, which gives the error message "operation must use an updatable query"? Thanks a lot.
Expand|Select|Wrap|Line Numbers
  1. UPDATE publications set publications.pmcid = (select fname.pmcid from fname where fname.pmid=publications.pubmedid)
this following query works fine:
Expand|Select|Wrap|Line Numbers
  1. UPDATE publications inner join fname on fname.pmid=publications.pubmedid
  2. set publications.pmcid = fname.pmcid
Sep 5 '08 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,492
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.
Sep 6 '08 #2

P: 51
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.
Sep 9 '08 #3

NeoPa
Expert Mod 15k+
P: 31,492
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.
Sep 10 '08 #4

P: 51
Maybe i missed something, but I've read the troubleshoot page, as far as I understand, none of the items listed answers my question.
Sep 10 '08 #5

NeoPa
Expert Mod 15k+
P: 31,492
Let me have a dig and see if I can find a clear explanation for you Annie...
Sep 10 '08 #6

NeoPa
Expert Mod 15k+
P: 31,492
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.
Sep 10 '08 #7

P: 51
Thank you NeoPa.
I am sure the alternative SQL will be useful for people like me, I look forward to seeing them.
Sep 10 '08 #8

NeoPa
Expert Mod 15k+
P: 31,492
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.
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Publications]
  2. SET [PMCID]=(SELECT [PMCID]
  3.              FROM [FName]
  4.              WHERE FName.PMID=Publications.PubMedID)
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Publications] INNER JOIN [FName]
  2.     ON FName.PMID=Publications.PubMedID
  3. SET Publications.PMCID=FName.PMCID
Sep 10 '08 #9

Expert Mod 2.5K+
P: 2,545
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
Sep 10 '08 #10

NeoPa
Expert Mod 15k+
P: 31,492
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.
Sep 10 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.