Does Subquery nested in Update query work?

[anniebai]

Update contacts set Commonsid = 
(select Commonsid from contacts1 where contactid = contacts.contactid and Commonsid is not null)
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.

[Stewart Ross]

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

[anniebai]

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

[anniebai]

OK. I solved the problem. finally gave up subquery, but used the one below. Still don't why the subquery couldn't work.

UPDATE contacts as c, contacts1 as c1 SET c.eracommonsid = c1.eracommonsid
where c1.contactid = c.contactid

[NeoPa]

The way you probably should do this in Jet SQL (Access) is :

UPDATE Contacts as C INNER JOIN Contacts1 as C1
    ON C.Contactid=C1.Contactid
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.