By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

How do I make an UPDATE query with subquery?

Seth Schrock
Expert 2.5K+
P: 2,951
After searching online for awhile, I have discovered many people with this same question, but no answers. I'm trying to create an update query that sets the value of the field to the result of a subquery. However, when I try to run the query I get an error message that says "Operation must use an updateable query." My research tells me that it is because of the subquery. Here is what I have so far:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblACHFiles INNER JOIN tblInvoices ON tblACHFiles.InvoiceID = tblInvoices.InvoiceID 
  2. SET tblACHFiles.InvoiceID = (SELECT InvoiceID FROM tblInvoices WHERE CustomerID = Forms!frmCustomer!CustomerID
  3. AND BeginDate = Forms!frmCustomer!txtStartDate AND EndDate = Forms!frmCustomer!txtEndDate)
  4. WHERE (((tblACHFiles.[ACHCompanyID])=[Forms]![frmCustomer]![CustomerID]) 
  5. AND ((tblACHFiles.[EffectiveDate])>=[Forms]![frmCustomer]![txtStartDate] 
  6. And (tblACHFiles.[EffectiveDate])<=[Forms]![frmCustomer]![txtEndDate]));
  7.  
I suppose that I could replace the subquery with a DLookup() function, but I don't know the syntax to have three criteria. As those are the only two options that I know of, I don't know how to procede.
Nov 10 '11 #1

✓ answered by NeoPa

Check out Reasons for a Query to be Non-Updatable. This is Jet-specific, so you needn't worry that all SQL engines necessarily behave that way.

If you are unable to make your underlying query updatable then the only recourse is to create the data in a table then run the UPDATE query using that table. Probably best to delete or clear the temporary table after use I would suggest.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,709
Check out Reasons for a Query to be Non-Updatable. This is Jet-specific, so you needn't worry that all SQL engines necessarily behave that way.

If you are unable to make your underlying query updatable then the only recourse is to create the data in a table then run the UPDATE query using that table. Probably best to delete or clear the temporary table after use I would suggest.
Nov 11 '11 #2

Seth Schrock
Expert 2.5K+
P: 2,951
After reading the information in the link, I'm not sure where my query falls. However I did just come up with an idea. Would it be possible to use this as the set value:
Expand|Select|Wrap|Line Numbers
  1. SET tblACHFiles.InvoiceID = DLookup("InvoiceID", "qryFindInvoiceID")
qryFindInvoiceID would be what I did have in the subquery and would return only one record. Does this fall into the temporary table category or would this still be non-updatable? I'm not at work so I can't try it until Monday.

I've never worked with temporary tables so I'm not sure what this entails.
Nov 11 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
I would expect that to work Seth. I'm sure such techniques have been used before successfully. Still best to test it of course, but I'd expect it to work.
Nov 11 '11 #4

Seth Schrock
Expert 2.5K+
P: 2,951
It worked. Thanks for your help.
Nov 12 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
Pleased to hear it Seth :-)
Nov 12 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.