Oracle SQL - UPDATE using complex join

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 - 17:01:04 CDT