Solved

UPDATE error : Operation must use an updatable query

Posted on 1998-06-24
14
332 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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