[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

UPDATE error : Operation must use an updatable query

Posted on 1998-06-24
14
Medium Priority
?
344 Views
Last Modified: 2012-06-27
Situation :

I use Access 97 and I have created the 2 following table with sql commands :

create table PERSON (
      P_ID            COUNTER CONSTRAINT p_idc PRIMARY KEY,      
      P_NAME            varchar(50) NOT NULL CONSTRAINT p_namec UNIQUE
)

create table PRODUCT      (
      PR_ID            COUNTER CONSTRAINT pr_idc PRIMARY KEY,       
      PR_NAME            varchar(50) NOT NULL CONSTRAINT pr_namec UNIQUE,
      PR_COUNTRY      varchar(20),
      PR_ADMIN_ID      integer NOT NULL
)


I want to UPDATE the database with sql commands.

The following SQL pass-trough commands does not work :

      update PRODUCT set PR_COUNTRY='France',PR_ADMIN_ID=(select P_ID from PERSON where P_NAME='USER') where PR_ID=1

I get the apparently famous "Error 3035: operation must use an updatable query" error message.

BUT the following **does work** correctly :

      update PRODUCT set PR_COUNTRY='France',PR_ADMIN_ID=1 where PR_ID=1

The problem seems to come from the SELECT subquery in the UPDATE query. Is it possible to do this ?

I need to find an ANSI SQL solution, as the software I write will use ODBC and must be Oracle-compatible


Any idea ?

many thanks in advance,

Eric
0
Comment
Question by:efauquem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 6

Expert Comment

by:devtha
ID: 1975716
Are you using docmd runsql command?
docmd runsql "sql statement;"

0
 

Author Comment

by:efauquem
ID: 1975717
No, I type sql commands directly in Access :

New Request/SQL Specific...

Thanks for your interest ...

0
 
LVL 6

Expert Comment

by:bknouse
ID: 1975718
Change the data type for the PR_ADMIN_ID field to LONG INTEGER
or use the CLng() function.  That is the data type for the counter field (P_ID).  Otherwise the sql looks correct.

Brent
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 12

Expert Comment

by:Trygve
ID: 1975719
Could it be that your subselect returns more than one value/record ?

Since this is a pass-through query the CLng cannot be used. Instead the Convert function (or at least this applies for MS SQL Server) will do the trick.

update PRODUCT set PR_COUNTRY='France',PR_ADMIN_ID=Convert(Long,(select P_ID from PERSON where P_NAME='USER')) where
    PR_ID=1
0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1975720
Where do you have your tables created? If it is in Access itself , why do you use pass-through query?
0
 

Author Comment

by:efauquem
ID: 1975721
I must have misdescribed my problem.

- i don't use VB, but direct sql queries to test the odbc calls of my Visual C++ project, so I won't have access to convert() function, only standard SQL in order to have portability between RDBMSs.

- I think the problem is using a select subquery in an update query with Access (everything is ok when I run this with Oracle)

- There is no problem with data types, as the following works perfectly.

insert into products select 'Product1',P_ID from PERSON where PR_NAME='USER'

- Any idea ?

Eric

(thanks a lot for your attention)

0
 
LVL 12

Accepted Solution

by:
Trygve earned 1200 total points
ID: 1975722
Try something like this;

update PRODUCT, PERSON SET PRODUCT.[PR_COUNTRY] = 'France', PRODUCT.[PR_ADMIN_ID] =  PERSON.[P_ID] WHERE ((PERSON.[P_NAME] = 'USER') AND (PRODUCT.[PR_ID] = 1))

The idea is to include both PRODUCT and PERSON table in the query. Set the criteria on both and have the query set the PR_ADMIN_ID field to the P_ID field from the PERSON table.

If you need more details, please let me know.
0
 

Author Comment

by:efauquem
ID: 1975723
Your suggestion has solved the problem.
Thanks a lot :-)

Eric

PS : well, in fact it works only with Access (and not Oracle), but I'll find a way through ...
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975724
Your Oracle problem could be caused by the square brackets surrounding the field references with underscore in their name. Access likes it this way, but I seem to remember that MS SQL Server (and probably Oracle) does not approve with the brackets.
0
 

Author Comment

by:efauquem
ID: 1975725
Hi,

Well, I've tried to remove the brackets sourrounding the field names and it still works with Access.
update PRODUCT, PERSON SET PRODUCT.PR_COUNTRY = 'Francisco', PRODUCT.PR_ADMIN_ID =  PERSON.P_ID
    WHERE ((PERSON.P_NAME = 'ERIC') AND (PRODUCT.PR_ID = 1))

Conclusion : Access doesn't care about brackets in SQL commands !


In fact, Oracle complains about the SET keyword missing in the request.
I think that Oracle does not support updating 2 tables at the same time.

Confirmed by Oracle documentation :only one table after the UPDATE keyword.

Funny thing : Access documentation does not seem to allow it neither :
"Syntax :
UPDATE table
    SET newvalue
    WHERE criteria;
"Copyright (c) 1996 Microsoft Corporation


Thanks again for your help.

0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975726
What happens if you remove Person from the update part.

update PRODUCT SET PRODUCT.PR_COUNTRY = 'Francisco', PRODUCT.PR_ADMIN_ID
       =  PERSON.P_ID
           WHERE ((PERSON.P_NAME = 'ERIC') AND (PRODUCT.PR_ID = 1))

Perhaps this will give an error message ?

Have a nice weekend !
0
 

Author Comment

by:efauquem
ID: 1975727
Oracle does give an error message in this case.

"Unknown column name" for PERSON.P_NAME.

Too bad ...

Eric

0
 

Expert Comment

by:Rusfist
ID: 2663166
Listening...
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2663284
???
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question