as400 - update query problem  | |
May 29th, 2009, 07:07 PM
|
#1 (permalink)
| | Junior Member
Join Date: May 2009
Posts: 1
| as400 - update query problem
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 |
| |
May 30th, 2009, 01:03 AM
|
#2 (permalink)
| | Anime Otaku
Join Date: Oct 2001 Location: Tampa, FL USA
Posts: 108,969
|
Welcome to TechIMO!
I am moving your inquiry to the programming and development forum.
__________________ Robert Richmond | TechIMO Community Relations Director
Infinite perceptions. One reality. FanFiction.Net - Unleash your imagination. |
| |
May 30th, 2009, 10:30 AM
|
#3 (permalink)
| | Caveat Emptor
Join Date: Mar 2005 Location: Out of my mind
Posts: 3,241
|
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 |
| |
May 30th, 2009, 11:15 AM
|
#4 (permalink)
| | Not Really a Member
Join Date: Oct 2001
Posts: 25,384
| 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  |
| |
May 30th, 2009, 08:37 PM
|
#5 (permalink)
| | Caveat Emptor
Join Date: Mar 2005 Location: Out of my mind
Posts: 3,241
|
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  )
Last edited by Rootstonian : May 30th, 2009 at 08:46 PM.
|
| | | Thread Tools | Search this Thread | | | | |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | | | | Most Active Discussions | | | | | Recent Discussions  | | | | | |