Query not updateable (1 Viewer)

bongbang

Registered User.
Local time
Today, 10:29
Joined
Dec 28, 2017
Messages
17
Trying to update a table with a (single) result from another table, I got an "Operation must use an updateable query" with this query:

Code:
update Service_case s
set s.owner_id = (select c.id from contact c where c.contact_name = "foo")
where s.device_sn = "bar"

Is the problem due to the potential of the sub-query's returning more than one result (although that's not the case here)? I know that if I replaced the sub-query with, say, 4, then it would work.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Jan 23, 2006
Messages
15,364
This Allen Browne link shows most common reasons for the "updateable query" message.

Could you post a copy (in zip format) of the database (sufficient to show the issue)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
For a query to be updateable, ALL parts of the query must be updateable.

Here are the 2 most common causes:

1. is any part of the query or subquery aggregating data?
2. If the tables are SQL Server or other RDBMS, Access will only allow updating if the table has a primary key or unique index.

Hard to say exactly what your problem is without seeing the real query and possibly the actual database.
 

bongbang

Registered User.
Local time
Today, 10:29
Joined
Dec 28, 2017
Messages
17
Thanks for the suggestions, but are we sure that my query (w/ the subquery) is valid for MS Jet SQL? I tried an analogous query on a different pair of tables and it doesn't work, either. Here, too, when I replace the subquery with a number, it works. So I still suspect that it's the subquery syntax that's to blame.
 

bongbang

Registered User.
Local time
Today, 10:29
Joined
Dec 28, 2017
Messages
17
Pat,

No to both of your questions, but the "id" field in the subquery is auto-number. Does that make the query "non-updateable"?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 28, 2001
Messages
26,999
If the subquery is not clearly distinct (perhaps because your selector isn't based on the prime key) then I think it will fail for just about any ANSI standard SQL. An update must be done FROM a single value, though it COULD be done to any number of rows that meet the outer query's WHERE conditions.

That syntax is correct in the case where the sub-query WHERE clause produces a unique value. NOTE: Even if it happens that the given sub-query WHERE is unique IN THE GIVEN CASE, if it turns out that the general cardinality of the sub-query is not 1, I think it objects to the attempt. So if at least one case exists in your contacts table c where multiple c.id values can share the same c.contact_name, that is where you are hosed. In this case, it is not the returned value c.id but the selector value c.contact_name that must be unique.
 

bongbang

Registered User.
Local time
Today, 10:29
Joined
Dec 28, 2017
Messages
17
Thank you, Doc Man. I think you're getting me somewhere. Interestingly, though, c.contact_name does have a unique constraint (indexed, no duplicates), but apparently that isn't enough.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 28, 2001
Messages
26,999
Interesting. If your contact name is indeed declared as unique, then Access should have been able to determine that and allow the update. The cardinality would have been 1 when it evaluated the sub-query. I have used that kind of syntax myself for some update and insert queries, so I know it is legal.

The only other thing that I can see as an odd possibility is if the Service_Case entity is a query rather than a table AND it includes a computation. OR if the table contains a lookup field not involving device_sn, which might make the record selection process ambiguous.
 

JHB

Have been here a while
Local time
Today, 18:29
Joined
Jun 17, 2012
Messages
7,732
Create a query (call it FindContact) to pick the Id from the Contact table.
Code:
SELECT c.id
FROM contact AS c
WHERE c.contact_name="foo";
Code:
UPDATE Service_case, FindContact SET Service_case.Owner_id = [FindContact].Id
WHERE Device_sn="bar";
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
JHB,
That won't work because there is no join between the two tables. The cross join produces a Cartesian Product and those are not updateable.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 28, 2001
Messages
26,999
update Service_case s
set s.owner_id = (select c.id from contact c where c.contact_name = "foo")
where s.device_sn = "bar"

OK... plain and simple. Here is your original query. The part in RED must produce a unique result. You claim that it does because the contact name is marked No Dups. That is good. The part in Green does NOT have to be unique. If it is not, then multiple matching records would be updated.

HOWEVER, Pat brings up an interesting point. Syntactically there is a difference between your query and the solution offered by JHB - but SEMANTICALLY, both have the same effect: The sub-query must be executed first, after which the outer query would have to execute. When SQL tries to do this, it sees this as a potential Cartestian JOIN and might balk at that point. So here is a question that might move this along.

Does there exist a relationship (formal or not) between your contact and service case tables? Because if there IS, you could do this with a formal JOIN that would probably work quite well here and would eliminate the implied cross-JOIN.
 

bongbang

Registered User.
Local time
Today, 10:29
Joined
Dec 28, 2017
Messages
17
Thanks for the follow-up, Doc Man. Here are my responses to the points you raised.

1. Service_case is indeed a table.
2. The only relationship between between the two tables is the very field that we're trying to update, so join wouldn't work here. (I've used join with updates before with great success, but this is different.)

I've come to the conclusion (yet unverified) that subquery's condition may have to be the primary key for it to work.

This is not an insurmountable obstacle, of course. In fact, I accomplished the update I had to do right after asking this question just by using two separate queries. I was just curious why the original query didn't work. Now thanks to you, I have an idea.
 

JHB

Have been here a while
Local time
Today, 18:29
Joined
Jun 17, 2012
Messages
7,732
JHB,
That won't work because there is no join between the two tables. The cross join produces a Cartesian Product and those are not updateable.
Actually in this case, it works.
 

Attachments

  • UpdateATable.accdb
    408 KB · Views: 108

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
Interesting. Perhaps ACE has gotten smarter and will do this because the query returns only a single row.
 

JHB

Have been here a while
Local time
Today, 18:29
Joined
Jun 17, 2012
Messages
7,732
Or perhaps you remember wrong! ;)
 

Users who are viewing this thread

Top Bottom