INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

UPDATE SQL FOR QUERY400 - AS400 STRSQL

UPDATE SQL FOR QUERY400 - AS400 STRSQL

(OP)
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

RE: UPDATE SQL FOR QUERY400 - AS400 STRSQL

Something along these lines ...

CODE

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 www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

RE: UPDATE SQL FOR QUERY400 - AS400 STRSQL

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

RE: UPDATE SQL FOR QUERY400 - AS400 STRSQL

Give it a try :

CODE

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 )

RE: UPDATE SQL FOR QUERY400 - AS400 STRSQL

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close