Re: Update from 3way join

Vlad,

nvl is used to give a value when your query would otherwise return a null. i.e. in the code below if the b.loc_postcode is null then '0000' will be returned.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"Vlad" <vtsyrlin_at_bigfoot.com> wrote in message news:bc7620c4.0205072153.445e98c6_at_posting.google.com...
> 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

>

>


> > >

> > > 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'

> > >                 )