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
10  4100  NeoPa 32,566
Recognized Expert Moderator MVP
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.
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 32,566
Recognized Expert Moderator MVP
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.
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 32,566
Recognized Expert Moderator MVP
Let me have a dig and see if I can find a clear explanation for you Annie...
NeoPa 32,566
Recognized Expert Moderator MVP
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.
Thank you NeoPa.
I am sure the alternative SQL will be useful for people like me, I look forward to seeing them.
NeoPa 32,566
Recognized Expert Moderator MVP
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: David K |
last post by:
Hello,
I've been trying to make my db web applications to work in
my test server and I keep getting this everytime I try to
modidy the database:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
The curious thing is that in my web hosting provider's
|
by: jallegue |
last post by:
I am working with MS-Access 2002. The two tables that I am working with
are:
dbo_IDX_FRS_account_bal_by_month ==> this is a linked table to SQL
== local table
The query that is executed is
=============================
UPDATE dbo_IDX_FRS_account_bal_by_month INNER JOIN ON (dbo_IDX_FRS_account_bal_by_month.account = .account) AND (dbo_IDX_FRS_account_bal_by_month.acct_control =
|
by: Mr. x |
last post by:
Hello,
I have hosted my site in an hosting server.
I got the following message, while trying inserting a record to the database
:
Operation must use an updatable query.
For what I know, I need to change the permision for the user name :
IUSR_MACHINENAME, that can change the database file.
|
by: Bob |
last post by:
running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
|
by: cyber0ne |
last post by:
I'm having a bit of trouble adding records to a local .DB file (using a
Paradox connection string). I checked the file permissions, and for
testing purposes set Everyone to Full access. But it seems that I can
add one and only one record to the table, any other attempts to insert
a record generate the error:
"Operation Must Use An Updatable Query"
I can access the table via other means, delete that one record, try
again, same...
| | |
by: Enrique Rojas |
last post by:
Hello I have this sql that triggers the abovementioned error.
UPDATE ra SET ra.Year_1_Cost = (SELECT sum(conversion*Year_1_accom) AS
year1
FROM accomfundlevels
WHERE driver_id=168 And level_id=1)
WHERE ra.driver_id=5 And ra.level_id=0;
The Select statement works fine in isolation but when I put it into the
whole statement then it gives and error in access.
|
by: Ranjan kumar Barik |
last post by:
Hello everybody !
I am trying to insert into an access table using ADO in ASP. But i am getting the error message: "Operation must use an updatable query".
I have checked the security settings of the .mdb file.
Still it,s showing the same error.
I have also taken an output of the query using "response.write" and use it, by copying it, in the access database. It worked there but it,s not working through the code.
Any suggessions !
|
by: geolemon |
last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now.
Arg.
I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access.
I am trying to perform a calculation based on information in a few tables, and use the result to update another.
Should be simple, here's what counts from the tables...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
