Solved

UPDATE error : Operation must use an updatable query

Posted on 1998-06-24
14
331 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
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
 
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: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 300 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now