DB2 Update Multiple Columns Syntax

captainlaw · #1 (**permalink**) 05-20-11, 03:25

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!

tonkuma · #2 (**permalink**) 05-20-11, 06:50

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

captainlaw · #3 (**permalink**) 05-20-11, 10:15

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!

dav1mo · #4 (**permalink**) 05-20-11, 10:26

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

n_i · #5 (**permalink**) 05-20-11, 11:29

Code:

UPDATE table1 t1 
 SET (col1, col2) = (
  SELECT col3, col4 
  FROM  table2 t2 
  WHERE t1.col8=t2.col9
 )

himanshuadmin · #6 (**permalink**) 05-24-11, 06:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech