SQL update from liba/myfile to libb/myfile

50 pts.
Tags:
AS/400
SQL statements
How can I update a file with the same name (and fields) from one library to another? This is my SQL statement:
UPDATE m3fdbqa5/oagrhe set uychid = 'TESTSQL',
uysmcd = (SELECT uysmcd FROM mv4cdta001/oagrhe
where mv4cdta001/uycono = m3fdbqa5/uycono
and mv4cdta001/uyagno = m3fdbqa5/uyagno
and mv4cdta001/uycuno = substr(m3fdbqa5/uycuno,2,9))
The file in the MV4 library has the updated uysmcd field that needs to be copied to the same field in the M3 library. The uycuno field is off by one character, thus the substr function. When I run the command I get this error message: Column or global variable MV4CDTA001 not found. Any suggestions would be appreciated!  Thanks, CLK
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  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

    Here is a shot -- just a rough draft .. using the name A5 for file m3fdbqa5/oagrhe

    and A4 for mv4cdta001/oagrhe

    UPDATE m3fdbqa5/oagrhe A5

    set A5.uychid = 'TESTSQL',
        A5.uysmcd = (SELECT a4.uysmcd

        FROM mv4cdta001/oagrhe a4
       where a4.uycono = a5.uycono
          and a4.uyagno = a5.uyagno
         and a4.uycuno = substr(a5.uycuno,2,9))

    54,090 pointsBadges:
    report
  • CLK

    Thank you, philp11 - that solved the problem! 

    CLK

    50 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: