IBMi DB2 SQL UPDATE over 3 joined tables

Hi.  I am a TSQL user trying to make sense of DB2 SQL on the IBMi.  I need to populate a field with a percentage of a cost in another field, within the same table.  That would be simple enough but I need to filter on field values in 2 separate tables.  I think my problem is understanding the joins in DB2..  I put this together but I'm leery of running this code on my IBMi, even over test data.  
UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE exists(
    SELECT a.itnbr,a.smat,a.co1t,a.so1t,b.vndnr,c.vndnr,c.ittyp,c.b2cocd
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr               
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr               
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                               
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')    

Open in new window


This looks like it should run but examples I'm finding online lead me to believe that I need to repeat the filters after my enclosed select statement.  I also looked at using a merge statement but not sure it's a good fit here..?  thanks.
LVL 7
valmaticAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
So why not simply make a copy of the 831 table, run the query, and review the results?

Query looks fine, based on your description.
0
Gary PattersonVP Technology / Senior Consultant Commented:
Or just run the SELECT clause and verify it picked up the rows you expect.
0
tliottaCommented:
First, is this a valid restatement of your intent?
UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE exists(
    SELECT 1
        FROM    wklibj.itmrvbx831 a
           JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr
           JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr
        WHERE c.ittyp IN(3,4) and c.b2cocd = 'CHN' and
             (b.vndnr LIKE 'F%' or  c.vndnr LIKE 'F%') )

Open in new window

That's just to make sure we know what you're expecting.

Second, running the SELECT statement as you wrote it by itself should tell you most of what you want to know. Have you done that?
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

valmaticAuthor Commented:
Sorry, finally had time to circle back to this.  Thanks both of you for responding.  

Gary, I did as suggested and ran the select statement and figured out that my field ITTYP is a text string and not numeric.  That stopped the errors I was getting on select and pulled up about 3500 records, which I expected.  I ran the corrected update statement over test data and it ran but changed all records in my test version of the wklibj.itmrvbx831 file, about 65000 records.  It looks like the update portion of the statement is ignoring the select subquery.    What am I missing?  Is there a better way to get this update done?

Tliotta,  that is an accurate statement of what I want.  I get the same number of records when I run your select query but I expect I'd get the same results running your update query.    

thanks
0
Gary PattersonVP Technology / Senior Consultant Commented:
OK, problem is most likely that there is no correlation between UPDATE and EXISTS().  Try this instead:

UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE (itnbr, smat, co1t, so1t) in (
    SELECT a.itnbr,a.smat,a.co1t,a.so1t
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr              
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr              
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                              
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')    

The row value expression (a.itnbr,a.smat,a.co1t,a.so1t) should contain the same columns in the same order as the SELECT clause, but you only need to include the columns needed to uniquely identify a row.  So, for example, if itnbr is unique:

UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE itnbr in (
    SELECT a.itnbr
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr              
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr              
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                              
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
valmaticAuthor Commented:
Hi Gary,  That makes sense.  Updated records are less than expected but I think I'm back on track now..  I want to test a little more today before I close this up.  Yay!!!
1
tliottaCommented:
Gary's provided a useful answer, so I'll only add a couple items of explanation. Sometimes explanations allow understanding that helps many future works.

First, review results of a statement like this:
SELECT itnbr,smat,co1t,so1t FROM wklibj.itmrvbx831
   WHERE (itnbr, smat, co1t, so1t) in (
      SELECT 1
          FROM    wklibj.itmrvbx831 a
             JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr
             JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr
          WHERE c.ittyp IN(3,4) and c.b2cocd = 'CHN' and
             (b.vndnr LIKE 'F%' or  c.vndnr LIKE 'F%') )

Open in new window

By using the outer SELECT in place of an UPDATE, you can see what rows will be affected without any changes happening.

But more to the point of understanding. be aware of a basic difference between WHERE (itnbr, smat, co1t, so1t) in ( or WHERE itnbr in ( and WHERE exists(. In particular, WHERE exists( contains nothing to connect to any current row of the UPDATE file. The same file is referenced in the inner SELECT, but there's nothing that says the currently updated row is the one that "exists". The EXISTS() clause only verifies that some row "exists".

The alternative, however, is WHERE itnbr in (, and it implicitly specifies that a selected row must match "itnbr" that comes from the currently updated row.
1
valmaticAuthor Commented:
I'm really sorry.  I thought I bottled this up already.  Thanks so much for your help.  My project went pretty smoothly because of your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.