IBMi DB2 SQL UPDATE over 3 joined tables

valmatic
valmatic used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Gary PattersonVP Technology / Senior Consultant

Commented:
Or just run the SELECT clause and verify it picked up the rows you expect.
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?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

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
VP 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')

Author

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!!!
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.

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial