Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2018, 03:23 PM   #1
bongbang
Newly Registered User
 
Join Date: Dec 2017
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
bongbang is on a distinguished road
Query not updateable

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.

bongbang is offline   Reply With Quote
Old 01-12-2018, 03:32 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,210
Thanks: 47
Thanked 1,808 Times in 1,758 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Query not updateable

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)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 01-12-2018, 03:35 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,835
Thanks: 13
Thanked 1,298 Times in 1,237 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Query not updateable

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-12-2018, 05:07 PM   #4
bongbang
Newly Registered User
 
Join Date: Dec 2017
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
bongbang is on a distinguished road
Re: Query not updateable

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 is offline   Reply With Quote
Old 01-12-2018, 05:29 PM   #5
bongbang
Newly Registered User
 
Join Date: Dec 2017
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
bongbang is on a distinguished road
Re: Query not updateable

Pat,

No to both of your questions, but the "id" field in the subquery is auto-number. Does that make the query "non-updateable"?
bongbang is offline   Reply With Quote
Old 01-12-2018, 07:58 PM   #6
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,479
Thanks: 41
Thanked 1,001 Times in 912 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query not updateable

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 01-12-2018, 08:09 PM   #7
bongbang
Newly Registered User
 
Join Date: Dec 2017
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
bongbang is on a distinguished road
Re: Query not updateable

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.

bongbang is offline   Reply With Quote
Old 01-12-2018, 11:08 PM   #8
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,479
Thanks: 41
Thanked 1,001 Times in 912 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query not updateable

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 01-13-2018, 12:57 AM   #9
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,062
Thanks: 2
Thanked 1,882 Times in 1,841 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Query not updateable

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";
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 01-13-2018, 07:11 AM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,835
Thanks: 13
Thanked 1,298 Times in 1,237 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Query not updateable

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-13-2018, 07:34 AM   #11
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,479
Thanks: 41
Thanked 1,001 Times in 912 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query not updateable

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 01-13-2018, 06:06 PM   #12
bongbang
Newly Registered User
 
Join Date: Dec 2017
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
bongbang is on a distinguished road
Re: Query not updateable

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.
bongbang is offline   Reply With Quote
Old 01-13-2018, 11:27 PM   #13
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,062
Thanks: 2
Thanked 1,882 Times in 1,841 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Query not updateable

Quote:
Originally Posted by Pat Hartman View Post
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.
Attached Files
File Type: accdb UpdateATable.accdb (408.0 KB, 20 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 01-14-2018, 11:42 AM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,835
Thanks: 13
Thanked 1,298 Times in 1,237 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Query not updateable

Interesting. Perhaps ACE has gotten smarter and will do this because the query returns only a single row.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-14-2018, 02:57 PM   #15
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,062
Thanks: 2
Thanked 1,882 Times in 1,841 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Query not updateable

Or perhaps you remember wrong!

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
updatable query error , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updateable query rustersen Queries 1 10-25-2013 04:21 AM
Form with Updateable and Non-Updateable Recordsource dancole42 Forms 1 01-09-2013 10:39 AM
Why is this query not updateable? nschroeder Queries 7 02-28-2012 02:16 PM
Operation must use an updateable Query message on a simple update Query piersonb Queries 0 03-16-2010 10:57 AM
Updateable form, not updateable query? IqbalS Forms 7 07-19-2008 10:31 AM




All times are GMT -8. The time now is 07:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World