iSeries Update Values in a Table Based on Values in Two Tables.

25 pts.
Tags:
AS/400 DB2
DB2 administration
i5/OS
iSeries
iSeries Access
I want to update a Client Category Code in the Client_Table where the Client has a specific Country Code AND and the Clients VAT No is Blank, Which is in The Client_Account Table in another Library.
The Matched Records are [LIBRARY1]FILE1.CLLDRS and [LIBRARY2]FILE2.ADLDRS
So Update [LIBRARY1]FILE1 SET CATCDE='EXM' where FILE1.CTRY = 'US' and [LIBRARY2]File2.VATNO =NULL.
I know the above syntax isn't correct. but can anyone tell me how to do this I have 80,000 records to update. I also could do it over multiple Country Codes at once is this a list test?


Software/Hardware used:
i5/OS

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    In one condition you have:
    ... WHERE FILE1.CTRY = 'US'
    That part seems direct enough. There shouldn't be any need for anything beyond that for the first condition. For the other condition, I assume that this would produce a result set of all CLIENTs that met that condition:
    SELECT file2.CLIENT FROM [LIBRARY2]File2 WHERE File2.VATNO =NULL
    If that's true, then a basic subselect might be sufficient:
    Update [LIBRARY1]FILE1 SET CATCDE='EXM' where FILE1.CTRY = 'US' and 
             file1.CLIENT in(SELECT file2.CLIENT FROM [LIBRARY2]File2 WHERE File2.VATNO =NULL
    If that works and if it performs good enough, that should be all there is to it. I would first try something like this:
    SELECT file1.CLIENT, file1.CATCDE FROM [LIBRARY1]FILE1 where FILE1.CTRY = 'US' and 
             file1.CLIENT in(SELECT file2.CLIENT FROM [LIBRARY2]File2 WHERE File2.VATNO =NULL
    The output should let you do a visual review to see if all CLIENTs that needed to be included were there and that no CLIENT was missing that should be included. Once you can make the SELECT work, you can feel more secure with the UPDATE. Anything like a performance problem can be worked out after correct selection is accomplished. Tom
    125,585 pointsBadges:
    report
  • Sconna23
    OK Have got this working:
    SELECT FILE1.CLIENT, FILE1.CATCDE, FROM LIBRARY1/FILE1 WHERE
    FILE1.CTRY ='US' and FILE1.CLIENT in(SELECT CLIENT FROM      
    LIBRARY2/FILE2 WHERE VATNO =''
    For Some Reason NULL and NOT NULL are not working so I'm using <> and ='' to get it selecting the right records. What if I wanted Multiple Countries? I.E. the Eurozone?
    IS GB, DE, FR
    etc. or
    ISNOT GB, DE, FR.
    I'm fairly new to SQL I'm used to query's rather than statements.
    25 pointsBadges:
    report
  • TomLiotta
    What if I wanted Multiple Countries? I.E. the Eurozone?
    IS GB, DE, FR
    You can use something like:
    where FILE1.CTRY IN( 'US','GB','DE','FR')
    etc. or
    ISNOT GB, DE, FR.
    You can also use something like:
    where NOT FILE1.CTRY IN( 'US','GB','DE','FR')
    Tom
    125,585 pointsBadges:
    report
  • Sconna23
    Cool Thanks; The Select Statement is working. Now for the Update: :S Regards Dan.
    25 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following