ORACLE UPDATE SELECT 联合使用

table & sql 环境准备

1 要修改的数据

select empid,religion_code from PERSONAL_INFO_TEMP_WANG;

2 修改数据的来源

  SELECT  SP.CODE_NO, 
         S1.CONTENT CODE_NAME
        FROM SY_CODE        T,
            SY_CODE_PARAM  SP,
             SY_GLOBAL_NAME S1
       WHERE T.PARENT_CODE_NO = '124566'
          AND T.CODE_NO = SP.CODE_NO
          AND SP.CPNY_ID = 'I11'
         AND T.CODE_NO = S1.NO(+)
      AND S1.LANGUAGE(+)='en'
        AND T.ACTIVITY = 1; 

 

3 修改结果

RELIGION_CODE字段已经由相应的字符变为数字.

执行SQL

update PERSONAL_INFO_TEMP_WANG a -- 使用别名
   set religion_code =  (select b.CODE_NO 
                                from (SELECT SP.CODE_NO, S1.CONTENT CODE_NAME
                                        FROM SY_CODE        T,
                                             SY_CODE_PARAM  SP,
                                             SY_GLOBAL_NAME S1
                                       WHERE T.PARENT_CODE_NO = '124566'
                                         AND T.CODE_NO = SP.CODE_NO
                                         AND SP.CPNY_ID = 'I11'
                                         AND T.CODE_NO = S1.NO(+)
                                         AND S1.LANGUAGE(+) = 'en'
                                         AND T.ACTIVITY = 1  ) b
                               where upper(a.religion_code) = upper(b.CODE_NAME));

参考文章