correlated subquery in UPDATE (newbie)

[Richard Bond]

Hello,

The following query works in ORACLE but when run in ACCESS 2000 returns
"Operation must use an updateable query"

update tab x
set expshare = (select a.exp / b.exp
from tab a, tab b
where b.product = 1
and a.id = x.id
and a.time = b.time)

This is basically joining the table to itself and updating an existing field
based on a calculation on another field.

can anyone help?

regards,

Richard

 

[Michel Walsh]

Hi,


I would try:

UPDATE tab As x INNER JOIN tab As b ON x.time=b.time
SET x.expshare = x.exp / b.exp
WHERE b.product=1


Note that if there are more than one record in "b", for a given record in
"x", the record in "x" will be update more than once. Also, from what I
understand, all records with their product=1 will see their expshare updated
to 1.



Hoping it may help,
Vanderghast, Access MVP

 

[Richard Bond]

Sorry, I should have given you an example. I have saved an example of the
table in question at :

http://www.jburden.com/query.htm (in case the formatting below doesn't work
out)

Product Time Outlet Exp ExpShare
1 1 1 500 1
2 1 1 250 0.5
3 1 1 250 0.5
1 2 1 400 1
2 2 1 200 0.5
3 2 1 200 0.5
1 3 1 400 1
2 3 1 250 0.625
3 3 1 150 0.375
1 4 1 400 1
2 4 1 300 0.75
3 4 1 100 0.25
1 1 2 50 1
2 1 2 25 0.5
3 1 2 25 0.5
1 2 2 40 1
2 2 2 20 0.5
3 2 2 20 0.5
1 3 2 40 1
2 3 2 25 0.625
3 3 2 15 0.375
1 4 2 40 1
2 4 2 30 0.75
3 4 2 10 0.25


I have given an example of what the numeric results should be in the
ExpShare column. (Product 1 is always the denominator, "within Time and
Outlet")

Note in this example I have added an extra Outlet column as there could be
other fields that I need to "fix" on, so the oracle query for this would be:

update tab x
set expshare = (select a.exp / b.exp
from tab a, tab b
where b.product = 1
and a.time = b.time
and a.Outlet = b.Outlet)

the select statement below returns the ExpShare column in a recordset in
access - its just I can't get it back into the original table without
creating a temporary table.

(select a.exp / b.exp
from tab a, tab b
where b.product = 1
and a.time = b.time
and a.Outlet = b.Outlet)

It would really help if someone could do this all in one move.

regards,

Richard

 

[Richard Bond]

Brian Camire's answer was just what I wanted:

UPDATE
tab AS a,
tab AS b
SET
a.expshare = a.exp/b.exp
WHERE
b.product=1
AND
a.time=b.time
AND
a.outlet=b.outlet