Hallo Serge,

first really thanks for your help, I've been able to understand

what to do even if I had to modify it.

My AS/400 v5r1 doesn't like the AND (A.c, A.d) IN .... line !

So, just for your information, I changed it like this (after MANY try):

update ORCLR01L A

SET A.ocmagr = 'T', A.ocfilr = 'T ', A.ocnumr = A.ocnumr + 42000

WHERE A.occlir = '008137' and A.ocansr = 2004

AND A.ocmagr IN (SELECT ocmagt FROM ORCLT01L B

WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')

AND A.ocfilr IN (SELECT ocfilt FROM ORCLT01L B

WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')

AND A.ocnumr IN (SELECT ocnumt FROM ORCLT01L B

WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')

I think it's a bit redundant but... it worked.

From the manuals I could't figure out how to write the

AND (A.c, A.d) statement as there was always an error for the

comma or anything else after the first field...

Thank you very much and Ciao, Andrea

"Serge Rielau" <sr*****@ca.eye-bee-em.com> ha scritto nel messaggio

news:sP***************@news04.bloor.is.net.cable.r ogers.com...

Andrea,

UPDATE A

SET A.a = 'X', A.b = 'X'

WHERE A.a = 'D' and A.b = 'D '

AND (A.c, A.d) IN (SELECT B.c, B.d

FROM B WHERE B.a = '*' and B.b = '*');

There are also solutions using EXISTS.

The newest SQL Standard (and DB2 UDB for LUW) supports a MERGE statement

which you can sue for more complex scenarios.

Cheers

Serge