UPDATE error : Operation must use an updatable query
Posted on 1998-06-24
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_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,