UPDATE SQL FOR QUERY400 - AS400 STRSQL

[tgomes]

The code below works in a SQL server environment, when I try to use the same in AS400 STrSQL I get a error message that I can not use Inner on a Update query.

UPDATE PWPTFILES/UPRM A INNER JOIN TGOMES/THUCSRMT B ON A.UPPRM =
B.UCPRM) INNER JOIN TGOMES/WRATELISTT C ON (C.UCSCOLD = B.UCSCH)
AND (B.UCSIZ = C.UCSIZ) AND (A.UPTYP = C.UPTYP)

SET B.UCSCH = C.UCSCHNEW
WHERE ((B.UCTYP='W') AND (B.UCACT=0) AND (B.UCCLD=0) AND

(A.UPRTE Not IN ( 0,1000,4000) AND C.UPTYP ='COM ')

How can I rewrite this into sql 400 and work!

Thanks

 

[sathyarams]

Something along these lines ...

UPDATE THUCSRMT b set UCSCH=(select c.UCSCHNEW from   WRATELISTT C,UPRM A where C.UCSCOLD = B.UCSCH and B.UCSIZ = C.UCSIZ and A.UPTYP = C.UPTYP and A.UPPRM = B.UCPRM and A.UPRTE Not IN ( 0,1000,4000) AND C.UPTYP ='COM '  ) 
where (B.UCTYP='W') AND (B.UCACT=0) AND (B.UCCLD=0)

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[tgomes]

It does not work. the second I try to join the uprm to thucsrmt, it gives me an error.

 

[sathyarams]

Can you post the statement you are trying and of course, the error message.

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Mercury2]

Give it a try :

UPDATE TGOMES/THUCSRMT B 
SET B.UCSCH = ( Select C.UCSCHNEW 
FROM PWPTFILES/UPRM A 
INNER JOIN TGOMES/WRATELISTT C 
ON C.UCSCOLD = B.UCSCH
Where A.UPPRM = B.UCPRM
And   B.UCSIZ = C.UCSIZ 
AND   A.UPTYP = C.UPTYP 
AND   C.UPTYP ='COM '
And   A.UPRTE Not IN ( 0, 1000, 4000 ) 
And   B.UCTYP ='W' 
And   B.UCACT = 0 
And   B.UCCLD = 0 )
		 
Where Exists ( Select 1 
FROM PWPTFILES/UPRM A 
INNER JOIN TGOMES/WRATELISTT C 
ON C.UCSCOLD = B.UCSCH
Where A.UPPRM = B.UCPRM
And   B.UCSIZ = C.UCSIZ 
AND   A.UPTYP = C.UPTYP 
AND   C.UPTYP ='COM '
And   A.UPRTE Not IN ( 0, 1000, 4000) 
And   B.UCTYP ='W' 
And   B.UCACT = 0 
And   B.UCCLD = 0 )

 

[tgomes]

Thank You, I had rewritten and use the exists clause like you did and it works... I just read somewhere that the only way to update in as400 and inner join is to use the EXISTS...

Thank you for your help.

Thais