Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Red face Unanswered: Update in DB2 using inner join for 3 tables

    Hello and Good day to everyone!

    First of all, I am sorry if this has been already posted before... I am a newbie actually, both in using DB2 and in programming and I could use some advice from you guys...

    Anyway, I want to Update a single column in a table but the parameters for this specific query will come from 2 other tables... I was hoping you can help me with this one...

    Here is a rough description of the situation I have:

    Table 1 has 3 columns... AAA, BBB, CCC. I want to change the value of CCC from 'A' to 'S'. Changes will affect multiple rows.

    Table 2 has 4 columns... MMM, NNN, OOO, PPP. Column NNN in Table 2 is equal to Column BBB in table 1. No keys (primary or foreign keys are used). Also, Column AAA in table 1 is equal to Column PPP in table 2.

    Table 3 has 3 columns... XXX, YYY, ZZZ. Column XXX in table 3 is equal to Column AAA in Table 1 and column PPP in table 2. Column ZZZ is equal to column PPP in table 2 and AAA in table 1.

    I need to update the value of column CCC in table 1 from 'A' to 'S'

    UPDATE TABLE1 T1 SET T1.CCC='S'


    And here are the conditions:

    only change data if T1.BBB='test1' and T2.OOO='12345' and T3.YYY != 'graph 1' or T3.YYY != 'graph 2'

    =======

    can you guys help me? any suggestion would be fine... If its possible without using JOIN, I am okay with it also.

    Any help or inputs would be appreciated. Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is basic SQL. Read this:

    DB2 SQL Cookbook

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    T1.BBB='test1' and T2.OOO='12345' and T3.YYY != 'graph 1' or T3.YYY != 'graph 2'
    Is it equivalent to
    Case 1:
    ( T1.BBB = 'test1' and T2.OOO = '12345' and T3.YYY != 'graph 1' ) or T3.YYY != 'graph 2'

    or
    Case 2:
    T1.BBB = 'test1' and T2.OOO = '12345' and ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' )

  4. #4
    Join Date
    Sep 2012
    Posts
    6
    @ARWinner: thank ou very much. I will look into this one.

    @tonkuma: its equivalent to case 2

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Suppose,
    if T3.YYY = 'xxxxxxx' (not equal to 'graph 1' nor 'graph 2')
    ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' ) is True.

    if T3.YYY = 'graph 1', then T3.YYY != 'graph 2' is True
    ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' ) is True.

    if T3.YYY = 'graph 2', then T3.YYY != 'graph 1' is True
    ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' ) is True.

    If T3.YYY is null then
    ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' ) is Unknown.

    As a consequence,
    T1.BBB = 'test1' and T2.OOO = '12345' and ( T3.YYY != 'graph 1' or T3.YYY != 'graph 2' )
    is equivalent to
    T1.BBB = 'test1' and T2.OOO = '12345' and T3.YYY IS NOT NULL


    I suspected that "or" might be "and",
    or the condition might be
    T1.BBB = 'test1' and T2.OOO = '12345' and T3.YYY NOT IN ('graph 1' , 'graph 2')

  6. #6
    Join Date
    Sep 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    I suspected that "or" might be "and",
    or the condition might be
    T1.BBB = 'test1' and T2.OOO = '12345' and T3.YYY NOT IN ('graph 1' , 'graph 2')
    Yes, you are correct... it should be AND not OR... sorry

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Table 2 has 4 columns... MMM, NNN, OOO, PPP. Column NNN in Table 2 is equal to Column BBB in table 1.
    No keys (primary or foreign keys are used). Also, Column AAA in table 1 is equal to Column PPP in table 2.
    Do you mean like this?
    t2.nnn = t1.bbb AND t1.aaa = t2.ppp


    Table 3 has 3 columns... XXX, YYY, ZZZ. Column XXX in table 3 is equal to Column AAA in Table 1 and column PPP in table 2.
    Column ZZZ is equal to column PPP in table 2 and AAA in table 1.
    Do you mean like this?
    t3.xxx = t1.aaa AND t3.xxx = t2.ppp
    AND t3.zzz = t2.ppp AND t3.zzz = t1.aaa

  8. #8
    Join Date
    Sep 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    Do you mean like this?
    t2.nnn = t1.bbb AND t1.aaa = t2.ppp



    Do you mean like this?
    t3.xxx = t1.aaa AND t3.xxx = t2.ppp
    AND t3.zzz = t2.ppp AND t3.zzz = t1.aaa
    that is correct

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Consider to use an EXISTS predicate in which join t2 and t3.


    (2)
    If t2.ppp in second conditions were replaced by t1.aaa then
    Code:
        t2.nnn = t1.bbb AND t1.aaa = t2.ppp
    (AND)
        t3.xxx = t1.aaa AND t3.xxx = t1.aaa
    AND t3.zzz = t1.aaa AND t3.zzz = t1.aaa
    By removing redundant conditions, you will see relationship of t2 and t3(and join of t2 and t3) is not neccesary.
    Code:
        t2.nnn = t1.bbb
    AND t2.ppp = t1.aaa
    (AND)
        t3.xxx = t1.aaa
    AND t3.zzz = t1.aaa
    or
    Code:
        t2.nnn = t1.bbb
    AND t2.ppp = t1.aaa
    (AND)
        t3.xxx = t1.aaa
    AND t3.zzz = t3.xxx
    As a consequence,
    another way may be using two EXISTS predicates each for table2 and table3.

  10. #10
    Join Date
    Sep 2012
    Posts
    6
    @tonkuma: thank you very much...

    so will my UPDATE statement look like this?


    Code:
    UPDATE TABLE1 T1 SET T1.CCC='S' 
    WHERE TABLE2.nnn = t1.bbb
    AND TABLE2.ppp = t1.aaa
    (AND)
        TABLE3.xxx = t1.aaa
    I don't think this is allowed in DB2

  11. #11
    Join Date
    Sep 2012
    Posts
    6
    anyway, I was able to solve my problem....

    thank you @tonkuma for your help...

    also to @ARWinner, the book you gave me was a great help

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    anyway, I was able to solve my problem....
    Congratulations!

    Would you please publish your solution to share with other members in this forum including me.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •