Solved

UPDATE error : Operation must use an updatable query

Posted on 1998-06-24
14
337 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 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