Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

"Operation must be an updateable query" error

folks,

i am getting the error:

 "Operation must be an updateable query"

when i try to run the following quey from within MS Access 2003.

UPDATE STUDENTS SET SSN = (SELECT SOONER_ID.Sooner_ID  FROM SOONER_ID  WHERE SOONER_ID.SSN = STUDENTS.Student_ID)
WHERE Exists (SELECT SOONER_ID.Sooner_ID  FROM SOONER_ID  WHERE SOONER_ID.SSN = STUDENTS.Student_ID);

here is the table structure

STUDENTS
------------
SSN
Student_ID

SOONER_ID
-------------
SSN
Sooner_ID


If i modify the query like so:

 UPDATE STUDENTS SET SSN = '01234567891'
WHERE Exists (SELECT SOONER_ID.Sooner_ID  FROM SOONER_ID  WHERE SOONER_ID.SSN = STUDENTS.Student_ID);

it works fine. i've checked and i do have read/write access to the .mdb file in question.

what is the problem here?

Thanks.
0
Lou1
Asked:
Lou1
1 Solution
 
shanesuebsahakarnCommented:
The subselect is causing your query to be non-updatable - you'll need to remove this part. If your subselect can possibly return more than one record, this will definitely be the case. Try this:

UPDATE STUDENTS SET SSN=DLookUp("[Sooner_ID]","SOONER_ID","[SSN]=" & STUDENTS.Student_ID) WHERE Exists (SELECT SOONER_ID.Sooner_ID  FROM SOONER_ID  WHERE SOONER_ID.SSN = STUDENTS.Student_ID)

This will update SSN with the first located record in SOONER_ID - if there are multiple different values for the same Student_ID, it will not be predictable which one is used.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now