What's wrong with this SQL for Access?

[cathy]

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!

 

[Sylvain Lafontaine]

A wild guess here: try adding a TOP 1 statement in the subquery. It's also
possible that the error comes not from the SQL statement itself but from the
way you call it from Access/DAO/ADO/VBA.

 

[John Spencer]

Well, Access has no idea if the subquery will return zero, one, or many
values. So it automatically flags the query as non-updatable for the
update.

Well, simple just use Top 1 or one of the aggregate functions such as DMax.
NOPE, those are not updatable either. LOVERLY, now what do I do.

A method that might work or it might not. Try this first.
UPDATE t1 INNER JOIN t2
ON 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
SET T1.F1 = [T2].[FA]

A solution with smallish record sets
USE the VBA functions DLookup or DMax or DFirst or DLast or DMin

Assumptions:
FB is a number field
FC is a number field
FD is a text field

UPDATE T1
SET T1.F1 =
DLookup("FA", "T2", "FB+1 =" & F2 & " AND FC = " & F3 & " AND FD =""" &
F4 & """ AND ...")

You will have to finish the third (criteria) clause. Hint it will look like
a where clause without the word WHERE at the beginning. If you can't build
it post back with the field types and perhaps one of us volunteers will
build the thing for you.

 

[cathy]

Thanks, All, after spending all morning on this, I just ended up
selecting the data into a new table and updating from there. Thanks
for you help!

Well, Access has no idea if the subquery will return zero, one, or many
values. So it automatically flags the query as non-updatable for the
update.

Well, simple just use Top 1 or one of the aggregate functions such as DMax.
NOPE, those are not updatable either. LOVERLY, now what do I do.

A method that might work or it might not. Try this first.
UPDATE t1 INNER JOIN t2
ON 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
SET T1.F1 = [T2].[FA]

A solution with smallish record sets
USE the VBA functions DLookup or DMax or DFirst or DLast or DMin

Assumptions:
FB is a number field
FC is a number field
FD is a text field

UPDATE T1
SET T1.F1 =
DLookup("FA", "T2", "FB+1 =" & F2 & " AND FC = " & F3 & " AND FD =""" &
F4 & """ AND ...")

You will have to finish the third (criteria) clause. Hint it will look like
a where clause without the word WHERE at the beginning. If you can't build
it post back with the field types and perhaps one of us volunteers will
build the thing for you.

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!

 

[Jamie Collins]

Well, Access has no idea if the subquery will return zero, one, or many
values. So it automatically flags the query as non-updatable for the
update.

Well, simple just use Top 1 or one of the aggregate functions such as DMax.
NOPE, those are not updatable either. LOVERLY

John, Just to clarify your point. Access/Jet does not support the
standard SQL-92 syntax e.g.

UPDATE <table>
SET <column> = (<scalar subquery>)

Instead, one is supposed to use Access/Jet's own proprietary
UPDATE..JOIN syntax which can yield unpredictable results e.g. a join
on a non-key column on the unpreserved table may return more than one
row so which one will be used to UPDATE the preserved table? It may be
better to use the DLookup function because it returns a scalar.

Jamie.

--

 

[John Spencer]

Jamie,
Thanks for the additional clarification.


I agree that using DLookup will return one value while the join may return
several rows with a value. HOWEVER, that just means that DLookup will
return one of the available choices - (the first one it finds?). So either
method would seem to work more or less the same. That is you get one of the
available values saved to the record and you don't get to specify which.

If you know that you want the maximum or minimum value then you can and
probably should use DMax or DMin to get the "largest" or "smallest" value.

 

[Jamie Collins]

I agree that using DLookup will return one value while the join may return
several rows with a value. HOWEVER, that just means that DLookup will
return one of the available choices - (the first one it finds?). So either
method would seem to work more or less the same. That is you get one of the
available values saved to the record and you don't get to specify which.

Thanks. As you can probably tell I don't use DLookup myself; I guess I
hoped it would generate a cardinality error.

The UPDATE syntax is one of those things that is very hard to defend
about Jet

Jamie.

--