Re: Update from 3way join

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 08 May 2002 01:57:50 GMT
Message-ID: <yC%B8.98971$zN.47782965@twister.socal.rr.com>

I don't see how you can preserve the key with that schema. However, you should be able to do the update with...

update shaddr set sazipc = nvl((
  select b.loc_postcode
  from location b, shopper c, customer d     where shaddr.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

  ), '0000')
where sazipc = '0000';

Richard

Vlad wrote:
>
> 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 - 20:57:50 CDT