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

what's wrong with this SQL for access?

P: n/a
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!

Dec 11 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
cathy wrote:
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.

Dec 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.