Re: Update from 3way join

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<yC%B8.98971$zN.47782965_at_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:

Thanks Richard, will try asap and post if it works. btw, what's "nvl"?

> >
> > 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 Wed May 08 2002 - 00:53:43 CDT