Re: Oracle SQL - UPDATE using complex join

Alex,

This worked.

Thank you.

JD

afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0210090934.11577c4b_at_posting.google.com>...
> 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 Fri Oct 11 2002 - 16:09:39 CDT