Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: DB2 Update Multiple Columns Syntax

    Hello,
    I'm trying to update multiple columns with set. However, I'm not sure the correct syntax. Here's what I'm trying to do in SQL.

    update prod.opm_exception
    set partner=a.PARTNERNAME
    , placement=a.PLACEMENTNAME
    , partnerorg=a.PARTNERORG
    , network=a.NETWORK
    , updated=current date
    from prod.mapping a
    join prod.exception b on a.MktgCode=b.mktg_code

    Can someone point me to the correct syntax in DB2?
    Much appreciated!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see syntax of UPDATE statement, especially "assignment-clause".
    "assignment-clause" includes row-fullselect.

    Here is a partial copy from Information Center.
    Code:
    assignment-clause
    
       .-,----------------------------------------------------------------------------------------.   
       V                                                                                          |   
    |----+-column-name--+----------------------+--=--+-expression-+-----------------------------+-+--|
         |              | .------------------. |     +-NULL-------+                             |     
         |              | V                  | |     '-DEFAULT----'                             |     
         |              '---..attribute-name-+-'                                                |     
         |    .-,-------------------------------------.             .-,------------------.      |     
         |    V                                       |             V                (1) |      |     
         '-(----column-name--+----------------------+-+--)--=--(--+---+-expression-+-----+-+--)-'     
                             | .------------------. |             |   +-NULL-------+       |          
                             | V                  | |             |   '-DEFAULT----'       |          
                             '---..attribute-name-+-'             |                (2)     |          
                                                                  '-row-fullselect---------'
    You can see more detail in here.
    UPDATE - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    May 2011
    Posts
    11
    The diagram is very hard to read and understand.
    Can someone provide a simple DB2 syntax based on the query I provided? It is basically updating couple of columns when the two tables' join condition is met.

    Your help is appreciated!

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How do the rows returned from your join equate to the table you are updating? Otherwise it would be more of an insert to get one row of data from your updated table with every row from the join condition.
    Dave

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    UPDATE table1 t1 
     SET (col1, col2) = (
      SELECT col3, col4 
      FROM  table2 t2 
      WHERE t1.col8=t2.col9
     )

  6. #6
    Join Date
    Mar 2011
    Posts
    3
    hello,

    if u want to update multiple columns in a single update statement then use

    then we use syntax:

    update tablename set(columnname1,columnname2,columnname3)=(value1,v alue2,value3) where condition

    with this u can update multiple columns with single update statement

  7. #7
    Join Date
    Jun 2013
    Posts
    1
    for me this worked in DB2:
    update tablename set
    columnname1=value1,
    columnname2=value2,
    columnname3=value3
    where condition

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ArpadHunady,

    you are right!

    But, if the values came from another table,
    you might want to use n_i's solution.

    Quote Originally Posted by n_i View Post
    Code:
    UPDATE table1 t1 
     SET (col1, col2) = (
      SELECT col3, col4 
      FROM  table2 t2 
      WHERE t1.col8=t2.col9
     )

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
  •