Re: Oracle SQL - UPDATE using complex join

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 08 Oct 2002 23:02:48 GMT
Message-ID: <suJo9.60783$X9.11062573@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 Tue Oct 08 2002 - 18:02:48 CDT