UPDATE SELECT Statement on AS/400

30 pts.
Tags:
AS/400
STRSQL
I need to update over 700 rows in a file based on the values in another file.  I am trying to use the UPDATE SELECT statement on the AS/400 STRSQL command line but it keeps on updating all of the rows in the UPDATE statement (over 23,000 rows).  I need to learn how to successfully execute an UPDATE SELECT statement on the AS/400.  Thank you!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

update table1 set itritm = ccf010 where select ccf010 from table2 where ccf010 = ‘ 194’

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • philpl1jb
    Show us what you've got and what you want to have happen.
    54,090 pointsBadges:
    report
  • MSanford
    UPDATE TABLE1 SET ICRITM = ' 194' WHERE SELECT CCF010 FROM TABLE2 WHERE CCF010 = ' 194'
    30 pointsBadges:
    report
  • philpl1jb
    and is that what you want, or is there more you need.
    54,090 pointsBadges:
    report
  • lance2221357
    I think Table1 and Table2 must some relation .
    UPDATE TABLE1 SET  ICRITM =   (SELECT CCF010 FROM TABLE2 WHERE                                                                      Table.Field1=Table2.Field1 )
    270 pointsBadges:
    report
  • BigKat
    update table1 set itritm = (select ccf010 from table2 where table2.itritm = table1.itritm) where exists(select * from table2 where table2.ctritm = table1.itritm)
    9,460 pointsBadges:
    report
  • Florenjm

    This is one of few things I don’t like about DB2, it should be straight forward like everybody else’s SQL.

    But, here are two samples to do the updates.

     

    ** Single Field Update  *********

     Update  TableW

        Set (WINFO) =

    (Select  IINFO   from TableI

      Where  WSYS  = ISYS

        And  WNAME = INAME)

      Where (WSYS,   WNAME) in

     (Select ISYS,   INAME from TableI)

     

    ** Multiple field Update ********

     Update  TableP                           

        Set (PINFO,  PUSE) =                       

    (Select  WINFO,  WUSE   from TableW 

      Where  WSYS  = PSYS                          

        And  WNAME = PNAME)                        

      Where (PSYS,   PNAME)  in                    

    (Select  WSYS,   WNAME from TableW)

    240 pointsBadges:
    report
  • DataKuz
    You can use Where exists(select xyz from lib2.fileb where lib1.fila.field = lib2.filb.field)
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: