cathy wrote:
Quote:
UPDATE T1
SET T1.F1 =
>
(SELECT T2.FA
from T2
>
where
T1.F2=T2.FB+1
AND T1.F3= T2.FC
AND T1.F4= T2.FD
AND T1.F5= T2.FE+1
AND T1.F6= T2.FF
AND T1.F7= T2.FG
AND T1.F8= T2.FH
AND T1.F9= T2.FI);
>
I'm getting "Operation must use an updateable query" but that seems
inconsistent with the explanation of this error in the help. If I
select using the same syntax, it works. Thanks!
|
My recollection is that the syntax you display works in MS-SQL Server
but not in JET (since I do no JET these days my recollection is pretty
hazy.)
I believe this example of SQL will effect the same result you are
seeking:
CurrentDb.Execute "UPDATE (SELECT c.CompanyName, mlc.CompanyName "
_
& "FROM Customers AS c INNER JOIN MyLocalCustomers as mlc " _
& "ON C.CustomerID = MLC.CustomerID) SET
c.CompanyName=mlc.CompanyName"
This works in Access 2007. Of course, this is ACE and not JET but my
recollection, again hazy, is that this has always worked in JET.
As a general preference I always use JOINs; my experience with JOINs is
that they are accepted more often, and transferable among technolgies
more readily. They may or may not be faster, depending upon how they
are optimized; I don't know of any case where they are slower.
Thanks for a question for which the right answer is not, "Forget
Access, melt down your computer and become a head gigolo". This group's
quality is in free-fall.
