+ Reply to Thread
Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    May 2009
    Posts
    1

    Question 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

  2. #2
    Anime Otaku RobRich's Avatar
    Join Date
    Oct 2001
    Location
    Tampa, FL USA
    Posts
    115,858
    Blog Entries
    67
    Welcome to TechIMO!

    I am moving your inquiry to the programming and development forum.
    Robert Richmond | Infinite perceptions. One reality.
    TechIMO.com Editor-in-Chief

  3. #3
    Caveat Emptor Rootstonian's Avatar
    Join Date
    Mar 2005
    Location
    Out of my mind
    Posts
    3,328
    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

  4. #4
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,856
    Quote Originally Posted by Rootstonian View Post
    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

  5. #5
    Caveat Emptor Rootstonian's Avatar
    Join Date
    Mar 2005
    Location
    Out of my mind
    Posts
    3,328
    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 07:46 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. as400-query
    By nikolai1960 in forum Technical Support
    Replies: 0
    Last Post: July 30th, 2008, 01:26 AM
  2. AS400 Query Help Needed
    By tncamoman in forum General Tech Discussion
    Replies: 0
    Last Post: March 21st, 2006, 12:32 PM
  3. AS400 Query Help!!!!!!!!!!!!!
    By pea333nut in forum Webmastering and Programming
    Replies: 0
    Last Post: September 2nd, 2004, 12:54 PM
  4. Access query problem
    By SlipGun in forum Webmastering and Programming
    Replies: 9
    Last Post: April 15th, 2002, 09:24 PM
  5. Update Query in MS Access 2000
    By ctaylor in forum Webmastering and Programming
    Replies: 11
    Last Post: March 2nd, 2002, 04:45 PM

Tags for this Thread

Posting Permissions

  • You may post new threads
  • You may post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Recommended Sites: ResellerRatings Store Reviews