Hi.
Please bear with me coze I am getting little desperate and still
unable to make this work.
I have 4 tables.
Customer, Shopper, Shaddr, Location. Customer is linked to Location
via composite pk (location_id, department_id), Customer is linked to
Shopper via pk (shopper_id). Shopper linked to Shaddr via pk/fk
shrfnbr/sashnbr).
I need to update a sazipc (ie postcode) field in the Shaddr with the
value from Location table. There is no direct join between them. So I
have to join, Customer to Shopper to Shaddr and Customer to Location
so that I can get a unique postcode for each shopper in shaddr.
The following sql still won't work (I am running 8i) and comes up with
error:
ORA-01779: cannot modify a column which maps to a non key-preserved
table".
I know what this error means, but little confused on how to modify the
sql to avoid the error. Much appreciate any help!
update (select a.sazipc, b.loc_postcode
from shaddr a, location b, shopper c, customer d
where a.sashnbr=c.shrfnbr
and c.shlogid=d.shopper_id
and d.location_id=b.location_id
and d.department_id=b.department_id
and d.client_id=b.client_id
and d.location_id is not null
and a.sazipc='0000'
)
set sazipc=loc_postcode;
Received on Tue May 07 2002 - 19:15:53 CDT
