Re: update with select in sql

[rob@xxxxxxxxx]

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