RE: SQL Update

That worked.  Thanks!  Now for the next challenge.  The prior example
assumes unique keyed records (by KEYFLD) in FILE1 and FILE2.  Now, I'd
like to update FILE1 but KEYFLD in FILE1 is no longer unique.  KEYFLD in
FILE2 still is though.  I get the same "Null values are not allowed in
column FIELD1 in table FILE in LIBRARY" message again.

Any ideas on this one?

Thanks,
Dave

> ----------
> From:         David Morris[SMTP:dmorris@plumcreek.com]
> Reply To:     MIDRANGE-L@midrange.com
> Sent:         Friday, June 11, 1999 2:21 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      Re: SQL Update
> 
> David,
> 
> If you add the following and it should work.  I believe where is 
> one of the first things executed.
> 
> where exists (SELECT FIELD2 FROM LIBRARY/FILE2 B
> WHERE A.KEYFLD = B.KEYFLD)
> 
> David Morris
> 
> 
> >>> "Leland, David" <dleland@Harter.com> 06/11/99 11:23AM >>>
> At version 4.3 we can now update a field in one file with the value of
> a
> field in a different file.  I've seen examples and gone through the
> manuals.  I can get it to work a little, but I keep ending up with the
> following:
> 
> Null values are not allowed in column FIELD1 in table FILE in LIBRARY.
> 
> My SQL statement is as follows:
> UPDATE LIBRARY/FILE A SET FIELD1 = (SELECT FIELD2 FROM LIBRARY/FILE2 B
> WHERE A.KEYFLD = B.KEYFLD)
> 
> FIELD1 and FIELD2 have the same attributes.  The KEYFLD's are
> identical
> and unique in both file.  The problem is happening because a record
> doesn't always exist in FILE2 for every record in FILE1.  I simply
> want
> the statement to update FIELD1 in FILE1 only if there's a matching
> record in FILE2.  I've tried restructuring the statement every way I
> could possible think of.  Am I SOL or am I just missing something
> obvious?
> 
> Thanks,
> Dave
> 
> +---
> | 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
> +---
> 
+---
| 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
+---