Re: Update in multi table join

rkligman_at_powergrid.electriciti.com (Rick Kligman) wrote:
>I can't seem to get the SQL syntax correct in the following problem:
>
>2 Tables SALES and SALESTEMP
>
>Both of them have an ID that provide the link. Both of them have a field
>called PurchaseDate.
>
>The original order is inserted into the SALES table but there was an
>error on the system date. So I create SALESTEMP with the ID's in question
>and fill in the PurchaseDate with a bunch of different dates. All I want
>to do is update the PurchaseDate field in SALES with the value in the
>SALESTEMP PurchaseDate field. What is the syntax of the SQL statement?
>

Try

update SALES
set purchaseDate = ( select purchaseDate

                     from salestemp
                     where SALES.ID = SALESTEMP.ID )
where id in ( select id
              from salestemp )

You will probably want to index ID in salestemp if there are more then a couple of rows.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jun 16 1995 - 00:00:00 CEST