Update Query question -

[lok1234]

Hi all,

I have a very simple update query as below, it works fine in Access:

update a, (select c.refno, c.price, c.unit from c) as b 
set a.price=b.price, a.unit=b.unit 
where a.refno=b.refno

Right now, I need to sum the price and unit first before updating Table a. I just simply re-write the above query as below:

update a, (select c.refno, sum(c.price) as price, sum(c.unit) as unit from c group by c.refno) as b 
set a.price=b.price, a.unit=b.unit 
where a.refno=b.refno

If I run this query, Access reports an error: "operation must use an updateable query"

I believe the above concept (nested table) is runnable in SQL SErver. I just don't know why Access cannot process this kind of query.

Is it true that Access has limitation or I have syntax error? Is there any workaround in Access in doing the above query? I need this kind of query in my new application.

Thank you a lot

 

[The_Doc_Man]

That error means that there is some ambiguity in the record to which Access thinks you want to write.

update a, (select c.refno, c.price, c.unit from c) as b
set a.price=b.price, a.unit=b.unit
where a.refno=b.refno

Why would you not write two queries and do a JOIN, like this:

Call this query B:
SELECT refno, sum(price) as tprice, unit FROM c GROUP BY refno, unit;

Then

UPDATE a.price, a.unit, b.tprice, b.unit FROM a INNER JOIN b ON a.refno=b.refno SET a.price = b.tprice, a.unit=b.unit ;

I think Access is getting confused by that subquery because it includes an SQL aggregate as a sub-set of the implied JOIN.