Home > IT Answers > AS/400 > iSeries Update Values in a Table Based on Values in Two Tables.
Sconna23
25 pts.
 iSeries Update Values in a Table Based on Values in Two Tables.
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
ASKED: July 30, 2010  10:25 PM
UPDATED: August 2, 2010  1:09 PM
RELATED QUESTIONS
 
  Help
 Approved Answer - Chosen by Sconna23 (Question Asker)

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

ANSWERED:  Jul 30, 2010  11:15 PM (GMT)  by Sconna23
 
Other Answers:
Last Wiki Answer Submitted:  June 27, 2012  1:22 am  by    0 pts.
Latest Answer Wiki Contributors: 
To see other answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Sconna23
 25 pts.
 

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

TomLiotta
 108,135 pts.
 

Cool Thanks; The Select Statement is working. Now for the Update: :S

Regards

Dan.

Sconna23
 25 pts.