Re: Oracle SQL - UPDATE using complex join

In that case, maybe ...

update apple
  set bushel_price = '5.20',
  weight = '10.5'
where exists (
  select *
  from farm, producer
  where apple.name = producer.name
    and producer.location = farm.location     and producer.name = 'SHINY APPLE COMPANY'     and farm.location = 'JONES FARM'
  )
/

or more efficiently ...

update apple
  set bushel_price = '5.20',
  weight = '10.5'
where name = 'SHINY APPLE COMPANY'
and exists (
  select *
  from producer
  where apple.name = producer.name
    and producer.location = 'JONES FARM'   )
/

Richard

JD Uhler wrote:
>
> Richard,
>
> When I execute the SQL I get the message "insufficient priveleges".
> By design, I do not have access to UPDATE producer or location tables.
> I can only UPDATE the apple table.
>
> A step in the right direction - I did not receive any syntax-like
> errors.
>
> JD
>
> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<suJo9.60783$X9.11062573_at_twister.socal.rr.com>...
> > You can probably do this ...
> >
> > update (
> > select apple.bushel_price,
> > apple.weight,
> > farm.location as farm_location,
> > producer.name as producer_name
> > from apple, farm, producer
> > where apple.name = producer.name
> > and producer.location = farm.location
> > )
> > set bushel_price = '5.20',
> > weight = '10.5'
> > where producer_name = 'SHINY APPLE COMPANY'
> > and farm_location = 'JONES FARM'
> > /
> >
> > This technique requires that APPLE be "key preserved" within that inline
> > view. You can check the online documentation for a thorough description
> > of what "key preserved" requires. Basically, if the primary keys are
> > PRODUCER.NAME and FARM.LOCATION then it will probably work.
> >
> > Note: there's really no reason to join farm at all in this example
> > unless you're suggesting that the update shouldn't occur if the producer
> > rows were orphans.
> >
> >
> > Richard
> >
> >
> > JD Uhler wrote:
> > >
> > > Here is my scenario. I would like to UPDATE data in table APPLE, but
> > > the UPDATE needs criteria from tables PRODUCER and FARM.
> > >
> > > I don't want to update all GREEN ORGANIC apples bushels on the all of
> > > SHINY APPLE's farms (SMITH FARM, JOHNSON FARM, etc.). I only want to
> > > update the bushels of apples on the JONES FARM.
> > >
> > > Pseudeocode:
> > >
> > > UPDATE APPLE
> > > SET BUSHEL_PRICE='5.20', WEIGHT='10.5'
> > > WHERE
> > > COLOR='GREEN' AND
> > > TYPE='ORGANIC' AND
> > > PRODUCER.NAME='SHINY APPLE COMPANY' AND
> > > FARM.LOCATION='JONES FARM'
> > >
> > > JOIN: PRODUCER.NAME=APPLE.NAME
> > > JOIN: PRODUCER.LOCATION=FARM.LOCATION
> > >
> > > Changing the data structure is not a possibility at this time.
> > >
> > > What SQL statement do I use?
> > >
> > > Thank you
> > >
> > > JD
Received on Wed Oct 09 2002 - 19:48:31 CDT