UPDATE error : Operation must use an updatable query

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
efauquemAsked:
Who is Participating?
 
TrygveConnect With a Mentor Commented:
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
 
devthaCommented:
Are you using docmd runsql command?
docmd runsql "sql statement;"

0
 
efauquemAuthor Commented:
No, I type sql commands directly in Access :

New Request/SQL Specific...

Thanks for your interest ...

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
bknouseCommented:
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
 
TrygveCommented:
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
 
Victor SpiridonovCommented:
Where do you have your tables created? If it is in Access itself , why do you use pass-through query?
0
 
efauquemAuthor Commented:
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
 
efauquemAuthor Commented:
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
 
TrygveCommented:
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
 
efauquemAuthor Commented:
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
 
TrygveCommented:
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
 
efauquemAuthor Commented:
Oracle does give an error message in this case.

"Unknown column name" for PERSON.P_NAME.

Too bad ...

Eric

0
 
RusfistCommented:
Listening...
0
 
TrygveCommented:
???
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.