Problem with Access Update Statement

[Richard]

I am trying to run the following update statement on an Access 2000 database
table:


UPDATE CL
SET SofsbypandR = xx.mysum
FROM CLIENT CL
inner join (SELECT Pos, CityPair, sum(sumoffares) as mySum FROM Client Group
By Pos, CityPair) xx
ON CL.Pos = xx.Pos
AND CL.CityPair = xx.CityPair



I get the following error:


Syntax error (missing operator) in query expression 'xx.mysum
FROM CLIENT CL
inner join (SELECT Pos, CityPair, sum(sumoffares) as mySum FROM Client Group
By Pos, CityPair) xx
ON CL.Pos = xx.Pos
AND CL.CityPair = xx.CityPair'.

This update statement works in SQL Server 2000, but not in access.
Any ideas what I am doing wrong?

 

[rocco]

maybe you have to try:
UPDATE CL
SET SofsbypandR = (SELECT xx.mysum
FROM CLIENT CL
inner join (SELECT Pos, CityPair, sum(sumoffares) as mySum FROM Client Group
By Pos, CityPair) AS xx

 

[Richard]

Nope this didn't work. I had to edit it slightly to:

UPDATE CLIENT
SET SofsbypandR = (SELECT xx.mysum FROM CLIENT CL inner join (SELECT Pos,
CityPair, sum(sumoffares) as mySum FROM Client Group By Pos, CityPair) AS xx
ON CL.Pos = xx.Pos AND CL.CityPair = xx.CityPair)

But I got the following error:

Operation must use an updateable query

 

[rocco]

welcome in the club!!!!
Please read my post few lines below yours.
Title is "Operations must use updatable query".

Rocco

 

[Chris2]

Nope this didn't work. I had to edit it slightly to:

UPDATE CLIENT
SET SofsbypandR = (SELECT xx.mysum FROM CLIENT CL inner join (SELECT Pos,
CityPair, sum(sumoffares) as mySum FROM Client Group By Pos, CityPair) AS xx
ON CL.Pos = xx.Pos AND CL.CityPair = xx.CityPair)

But I got the following error:

Operation must use an updateable query

Richard,

Access generally refuses to allow UPDATE operations with any Query
that uses an aggregation function (SUM, COUNT, etc.) on the SELECT
clause, or with any Query Object referenced on the FROM clause that
uses an aggregation function.

UPDATE CLIENT
SET SofsbypandR =
(SELECT xx.mysum
FROM CLIENT CL
inner join
(SELECT Pos
,CityPair
,sum(sumoffares) as mySum
FROM Client
Group By Pos
,CityPair) AS xx
ON CL.Pos = xx.Pos
AND CL.CityPair = xx.CityPair)

It appears to me that the above UPDATE is attempting to set the
column SofsbypandR equal to all the possible results of the SELECT
query. I don't think it can do this (even if there were no
aggregation in any of the queries after the = operator).


Sincerely,

Chris O.

 

[rocco]

Hi Chris,
please read below my post "Operation must use an updatable query: part two"
It seems it is not a matter of aggregate functions....you can not use a
select statment at all in an update query.
This sounds odd to me, but....

Thanks for any further help

Rocco

 

[rocco]

second post of John Spencer below in my post "Operation must use an updatable
query: part two" will solve the problem.

Rocco

 

[Chris2]

Hi Chris,
please read below my post "Operation must use an updatable query: part two"
It seems it is not a matter of aggregate functions....you can not use a
select statment at all in an update query.
This sounds odd to me, but....

Thanks for any further help

Rocco

:

rocco,

I understand. (Note: I didn't say you could use a SELECT statement
on the SET clause.)


Sincerely,

Chris O.