|
This is a multipart message in MIME format. -- [ Picked text/plain from multipart/alternative ] It's important for people to understand why I had the second where in there, the following: WHERE james1.mykey1 concat james1.mykey2 in ( select james1.mykey1 concat james1.mykey2 from rob/james1 inner join rob/james2 on james1.mykey1=james2.mykey1 and james1.mykey2=james2.mykey2) One of two things would happen without it. 1) If your file does not allow nulls, like the examples given, the sql aborts. Because key A-A is not in the second file it would try to set MYDATA1 to null. 2) If your file does allow nulls, the values in the rows that didn't match would get changed. Because key A-A is not in the second file it would set MYDATA1 to null. On the other hand this may be what you want. And in that case, you can omit the WHERE, allow nulls and you're in like Flynn. Rob Berendt -- "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." Benjamin Franklin rob@dekko.com Sent by: midrange-l-admin@midrange.com 08/30/2002 01:17 PM Please respond to midrange-l To: midrange-l@midrange.com cc: Fax to: Subject: Re: update with select in sql This is a multipart message in MIME format. -- [ Picked text/plain from multipart/alternative ] CREATE TABLE ROB/JAMES1 (MYKEY1 CHAR (5 ) NOT NULL WITH DEFAULT, MYKEY2 CHAR (5 ) NOT NULL WITH DEFAULT, MYDATA1 CHAR (5 ) NOT NULL WITH DEFAULT) CREATE TABLE ROB/JAMES2 (MYKEY1 CHAR (5 ) NOT NULL WITH DEFAULT, MYKEY2 CHAR (5 ) NOT NULL WITH DEFAULT, MYDATA1 CHAR (5 ) NOT NULL WITH DEFAULT) INSERT INTO ROB/JAMES1 VALUES('A', 'A', 'A') INSERT INTO ROB/JAMES1 VALUES('B', 'B', 'B') INSERT INTO ROB/JAMES1 VALUES('C', 'C', 'C') SELECT * FROM ROB/JAMES1 MYKEY1 MYKEY2 MYDATA1 A A A B B B C C C INSERT INTO ROB/JAMES2 VALUES('B', 'B', '1') INSERT INTO ROB/JAMES2 VALUES('D', 'D', '4') SELECT * FROM ROB/JAMES2 MYKEY1 MYKEY2 MYDATA1 B B 1 D D 4 UPDATE ROB/JAMES1 SET JAMES1.MYDATA1= ( Select james2.mydata1 from rob/james2 where james1.mykey1=james2.mykey1 and james1.mykey2=james2.mykey2 ) WHERE james1.mykey1 concat james1.mykey2 in ( select james1.mykey1 concat james1.mykey2 from rob/james1 inner join rob/james2 on james1.mykey1=james2.mykey1 and james1.mykey2=james2.mykey2) SELECT * FROM ROB/JAMES1 MYKEY1 MYKEY2 MYDATA1 A A A B B 1 C C C Rob Berendt -- "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." Benjamin Franklin _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l or email: MIDRANGE-L-request@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.