Re: Oracle SQL - UPDATE using complex join

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 - 14:58:45 CDT