Re(2): SQL: UPDATE question

Hi Pete,

Thanks very much, but I'm still having trouble :-(

Basically, the problem seems to be that there are more records in Table1
than in Table2, so there are cases where the SELECT statement returns a
null value. I'm getting a "null values not allowed" error, and I don't
really want to allow them (if there is no match in Table2, I don't want to
do anything to Table1). Might there be a way to use a CASE statement test
for this? Or maybe start by selecting only the records in Table1 that do
have a match? I've tried a few things, but I haven't found anything that
works yet. . . .

Thanks again,

MIDRANGE-L@midrange.com writes:
>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/


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| 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
+---