Re: SQL: UPDATE question

At 15:20 06/07/2001, Mike Naughton wrote:
>I suspect this may be very simple, but I've been beating my head trying to
>figure it out.
>
>I have two tables:
>Table1 has key fields KeyOneA and KeyOneB, and two other fields fldOneC
>and fldOneD
>Table2 also has key fields KeyTwoA and KeyTwoB, and two other fields
>fldTwoE and fldTwoF
>
>For each record in Table1 where KeyOneA=KeyTwoA and KeyOneB=KeyTwoB, I
>want to set fldOneC=fldTwoE and fldOneD=fldTwoF.


You could use subqueries:

  UPDATE Table1 SET
    fldOneC = (SELECT fldTwoE FROM Table2
               WHERE KeyTwoA = KeyOneA
                 AND KeyTwoB = KeyOneB)
    fldOneD = (SELECT fldTwoF FROM Table2
               WHERE KeyTwoA = KeyOneA
                 AND KeyTwoB = KeyOneB)


Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---