Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Question Unanswered: One SQL statement e.g Update ...

    Hi. I have following scenario:
    Table A: columns :
    c1 c2 Key1
    ----------------
    'a' '1' 'X'
    'b' '2' 'X'
    'c' '3' 'X'
    'a' '4' 'P'
    'b' '5' 'P'
    'c' '6' 'P'
    =============

    Table B: Columns:
    f1 f2 f3 Key2
    ----------------------
    '1' '2' '3' 'Y'
    '4' '5' '6' 'Z'
    =================

    Table C: Columns:
    Key1 Key2
    -----------------
    'X' ?
    =============


    I would like to update C.Key2 with the value of B.Key2 for the condition such
    that (C.Key1 = A.Key1 and A.c1 = 'a' and A.c2 = B.f1)
    and (C.Key1 = A.Key1 and A.C1 = 'b' and A.c2 = B.f2)
    and (C.Key1 = A.Key1 and A.C1 = 'c' and A.c2 = B.f3)

    I came up with the solution below but wondering if there is better way to do it , for example, can I perform the update with the combination of some sort of JOINs:

    My solution:
    Created a view temp out of Table A as follows:

    Table Temp: Columns:
    n1 n2 n3 n4
    --------------------
    '1' '2' '3' 'X'
    '4' '5' '6' 'P'
    ===============

    then
    Update C set C.key2 = B.key2 where
    C.key1 = temp.n4 and temp.n1 = B.f1 and temp.n2 = B.f2
    and temp.n3 = B.f3

    Will greatly appreciate any suggestions.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the MERGE statement. Alternatively, you may want to read up on "scalar subselects".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Hi taru,

    you can do it with one statement:

    Code:
    update
       test_c
    set
       test_c.key2 =
    ( select
         key2
      from test_b
      where
           test_b.f1 = ( select c2 from test_a where c1 = 'a' and key1 = test_c.key1 )
       and test_b.f2 = ( select c2 from test_a where c1 = 'b' and key1 = test_c.key1 )
       and test_b.f3 = ( select c2 from test_a where c1 = 'c' and key1 = test_c.key1 ) )
    Test:

    Code:
    db2 =>select * from test_a@
    
    C1 C2 KEY1
    -- -- ----
    a  1  X
    b  2  X
    c  3  X
    a  4  P
    b  5  P
    c  6  P
    
      6 Satz/Sätze ausgewählt.
    
    db2 =>select * from test_b@
    
    F1 F2 F3 KEY2
    -- -- -- ----
    1  2  3  Y
    4  5  6  Z
    
      2 Satz/Sätze ausgewählt.
    
    db2 =>select * from test_c@
    
    KEY1 KEY2
    ---- ----
    X    ?
    
      1 Satz/Sätze ausgewählt.
    
    db2 =>update
    db2 =>   test_c
    db2 =>set
    db2 =>   test_c.key2 =
    db2 =>( select
    db2 =>     key2
    db2 =>  from test_b
    db2 =>  where
    db2 =>       test_b.f1 = ( select c2 from test_a where c1 = 'a' and key1 = test_c.key1 )
    db2 =>   and test_b.f2 = ( select c2 from test_a where c1 = 'b' and key1 = test_c.key1 )
    db2 =>   and test_b.f3 = ( select c2 from test_a where c1 = 'c' and key1 = test_c.key1 ) )
    db2 =>@
    DB20000I  Der Befehl SQL wurde erfolgreich ausgeführt.
    db2 =>commit@
    DB20000I  Der Befehl SQL wurde erfolgreich ausgeführt.
    db2 =>select * from test_c@
    
    KEY1 KEY2
    ---- ----
    X    Y
    
      1 Satz/Sätze ausgewählt.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That will change every row in the table because you do not have a WHERE clause on the outer UPDATE itself. If that is desired... fine.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by stolze
    That will change every row in the table because you do not have a WHERE clause on the outer UPDATE itself. If that is desired... fine.
    Hi stolze,

    what do you mean of "WHERE clause on the outer UPDATE itself"? According to the taru's example we should change all records in the table c. I have found no restrictions on c in the text.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I didn't say that anything was wrong with your statement, did I? I was just stating that it may not be applicable to all situations.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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