Re: Oracle SQL - UPDATE using complex join

john.uhler1_at_dot.state.wi.us (JD Uhler) wrote in message news:<33e91ac6.0210081401.53d2b447_at_posting.google.com>...
> 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
>

Standard approach would be to use subquery:

update apple
set BUSHEL_PRICE='5.20', WEIGHT='10.5'
WHERE
COLOR='GREEN' AND
TYPE='ORGANIC' AND
name in (select producer.name from producer, farm

         where producer.name = 'SHINY APPLE COMPANY'
           and farm.location = 'JONES FARM'
           and producer.location = farm.location)

Are you sure table APPLE is joined to table PRODUCER by NAME? If so, you can use NAME = 'SHINY APPLE COMPANY' condition directly in the update instead of subquery.

> 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 - 12:34:02 CDT