CODEWITHSUNDEEP
sqlibm-midrange
Sanjay
Sanjay

Reputation: 3

Update 2 Fields in a table with 2 Fields from another table for matching Key in AS400 SQL. Please help me with this SQl Query

Update 2 Fields in a table with 2 Fields from another table for matching Key in AS400 SQL. Please help me with this SQl Query. I tried below query, it is working but wanted to fine tune.

update      color0  as a 
  set A.COL_COL1PCT = ( select B.COL_COL1PCT 
                             from srpua/color0919 as b 
                             where A.COL_COLOR= B.COL_COLOR ) 
  ,   A.COL_CHGDTE = 20140919 
  ,  a.COL_CHGUSER ='SRPUA'                                              
 where A.COL_COLOR in (SELECT B.COL_COLOR  from srpua/color0919 as b)

Upvotes: 0

Views: 242

Answers (2)

Anoop Menon
Anoop Menon

Reputation: 31

The following updates both A.COL_COL1PCT and A.COL_COLOR fields. Its a tweak in Charles' answer:

update color0  as a 
set (A.COL_COL1PCT, A.COL_COLOR, A.COL_CHGDTE, A.COL_CHGUSER )
= ( select B.COL_COL1PCT, B.COL_COLOR, 20140919, 'SRPUA'    
      from srpua/color0919 as b 
     where A.COL_COLOR= B.COL_COLOR )

Upvotes: 0

Charles
Charles

Reputation: 23793

There's really no "fine tuning" to be done...

You might prefer an alternate syntax using a row value expression; assuming a relatively, v5r4(?) or so, version of DB2 for IBM i.

update      color0  as a 
  set (A.COL_COL1PCT,A.COL_CHGDTE, A.COL_CHGUSER )
    = ( select B.COL_COL1PCT, 20140919, 'SRPUA'    
          from srpua/color0919 as b 
         where A.COL_COLOR= B.COL_COLOR ) 
 where A.COL_COLOR in (SELECT B.COL_COLOR  from srpua/color0919 as b)

Upvotes: 1

Related Questions