AW: SQL Update with Select from Another file ?

Hi,

If you are already on release V5R4 try the following:
UPDATE UNICMFIL/SDVMSP
SET SAJSAG = 0
WHERE (SANMID, SAB8AA) in (SELECT CHGID, CHGIDV
FROM CHGSDV0);

If you are not yet on release V5R4 try the following:
(I'm assuming that SANMID and SAB8AA are Character. If not you have to
convert the fields into character with the scalar function CHAR)

UPDATE UNICMFIL/SDVMSP
SET SAJSAG = 0
WHERE (SANMID concat SAB8AA) in (SELECT CHGID concat CHGIDV
FROM CHGSDV0);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mark Allen
Gesendet: Tuesday, May 15, 2007 17:15
An: Midrange Systems Technical Discussion
Betreff: SQL Update with Select from Another file ?


OK, I've done this before but only when its a single field match as this:

UPDATE UNICMFIL/SDVMSP SET SAJSAG = 0 WHERE SANMID in (SELECT CHGID FROM
CHGSDV0)

Searched but not finding anything as I need the Where to contain 2 fields
(not just the SANMID) .

Basically I would need the WHERE to be 2 fields SANMID and SAB8AA and the
SELECT to be CHGID and CHGDIV.

i.e. SANMID=CHGID and SAB8AA=CHGIDV