as400 - update query problem

[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

[RobRich]

Welcome to TechIMO!

I am moving your inquiry to the programming and development forum.

[Rootstonian]

Yeah, that can get a little tricky. Probably need something like:

Update table A

set A.value = '100'
where A.id= '1234'

and exists (select 'x'
from table B
where B.whatever = 'xyz'
and B.whomever = 123)
yada, yada

[vass0922]

Quote:

Originally Posted by Rootstonian

Yeah, that can get a little tricky. Probably need something like:

Update table A

set A.value = '100'
where A.id= '1234'

and exists (select 'x'
from table B
where B.whatever = 'xyz'
and B.whomever = 123)
yada, yada

I thought about that, but he's joining on two columns so that doesn't work so well

can you do a PL/SQL type query with the where statement joining the tables?
tried doing some googling but as400 doesn't appear real popular for googlers

[Rootstonian]

Just add another "and exists"

Update table A

set A.value = '100'
where A.id= '1234'

and exists (select 'x'
from table B
where B.whatever = 'xyz'
and B.whomever = 123)
and exists (selext 'x'
from table C
where C.thisever = 'abc'
and C.thiswhom = 123')

As my DBA's always tell me, it's hard to know what to do without knowing the data and the key structures of the underlying tables and how the keys from one table matches the other ones.

And part of this is ringing some distant bell in my head...like you can't select a column and use it in a join of the table you're updating??? Sometimes, I just have to write a little code snippet to run this type of stuff or have the DBA do a backdoor update (they hate that )