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

Does Subquery nested in Update query work?

P: 51
Expand|Select|Wrap|Line Numbers
  1. Update contacts set Commonsid = 
  2. (select Commonsid from contacts1 where contactid = contacts.contactid and Commonsid is not null)
  3. where Commonsid is null
This query gives 'Operation must use an updatable query' error. I copied this query to SQLServer 2005, it works fine.

The table 'Contacts1' is copied table of 'Contacts', except that Contacts1 table has values in CommonsID field, while in 'Contacts', the values are null. Contactid is the key for both tables.

I am the database owner, there's no problem of read/write privileges.

Your inputs are highly appreciated.
Apr 9 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Access is fairly restrictive about what constitutes an updatable query, and what may work in SQL Server may not in Access. There is an MS Knowledge Base article at http://support.microsoft.com/kb/328828/en-us which provides further detail.

You will need to recast your update query to do without the subquery, I reckon.

-Stewart
Apr 10 '08 #2

P: 51
Hi. Access is fairly restrictive about what constitutes an updatable query, and what may work in SQL Server may not in Access. There is an MS Knowledge Base article at http://support.microsoft.com/kb/328828/en-us which provides further detail.

You will need to recast your update query to do without the subquery, I reckon.

-Stewart
Thank you Stewart. The link is quite helpful for going through all the possibilities. But so far, non of them applies to my query.
could you give me a hint of how to translate this update query into a non-subquery query? thanks :-)
Apr 10 '08 #3

P: 51
OK. I solved the problem. finally gave up subquery, but used the one below. Still don't why the subquery couldn't work.
Expand|Select|Wrap|Line Numbers
  1. UPDATE contacts as c, contacts1 as c1 SET c.eracommonsid = c1.eracommonsid
  2. where c1.contactid = c.contactid
Apr 10 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
The way you probably should do this in Jet SQL (Access) is :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Contacts as C INNER JOIN Contacts1 as C1
  2.     ON C.Contactid=C1.Contactid
  3. SET C.eracommonsid=C1.eracommonsid
It may not make much performance difference on small datasets but should be more efficient as it uses the INNER JOIN.
Apr 11 '08 #5

Post your reply

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