Re: Update in multi table join
Date: 1995/06/19
Message-ID: <Pine.SUN.3.91.950619130309.21168A-100000_at_seatimes>#1/1
On 16 Jun 1995, 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.
I'll presume that ID is unique in both tables (ie, could be the primary key).
> 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
I take it then that not every ID in SALES has a corrosponding ID in SALESTEMP? This will cause the need to use a WHERE clause on the entire update to ensure we don't change rows in SALES that don't have anything in SALESTEMP.
> 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?
A good ol' corolated sub-query on the SET part.
To wit:
update SALES
set PURCHASEDATE = (select PURCHASEDATE from SALESTEMP
where SALES.ID = SALESTEMP.ID)
where ID in (select ID from SALESTEMP);
Have fun.
+----------------------------------------------------+ | Steve Butler Voice: 206-464-2998 | | The Seattle Times Fax: 206-382-8898 | | PO Box 70 Internet: sbut-is_at_seatimes.com | | Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA | +----------------------------------------------------+All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Mon Jun 19 1995 - 00:00:00 CEST