Re: update with select in sql

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.