用另一个表中的2个字段更新表中的两个字段以匹配AS400 SQL中的键.请帮我处理这个SQl查询

Question

用另一个表中的2个字段更新表中的两个字段，以匹配AS400 SQL中的Key。请帮我处理这个SQl查询。我尝试了下面的查询，它正在工作，但想微调。

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)  

Answer 1

真的没有"微调"要做。。。

您可能更喜欢使用行值表达式的替代语法；假设IBM i的DB2版本为v5r4（？）左右。

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) 

Answer 2

以下更新了A.COL_COL1PCT和A.COL_COLOR字段。这是查尔斯答案中的一个调整：

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 )