请高手帮忙,看看这个UPDATA怎样解决

Question

UPDATE TESTMDM M SET ( M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12,
                       M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23,
                       M.FC24,M.DATASOURCE_FROM)=(SELECT F.EMPLOYEENO,F.EMPLOYEENO,
                                                      F.EMPNAME,F.GENDER,F.BIRTHDAY,
                                                      F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME,
                                                      F.RESI_TYPE_CODE,F.MAR_STATUS_CODE,
                                                      F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE,
                                                      F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL,
                                                      F.CHARGE_RATE,F.COMM_CODE,'FC'
                                                   FROM FC_PSN F WHERE IS_MORE IS NULL 
                                                    AND F.EMPNAME=M.NAME_CH 
                                                    AND DECODE(F.GENDER,'男','1','女','2')=M.SEX
                                                    AND F.BIRTHDAY=M.BIRTHDATE
                                                    AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL)  
          WHERE M.FC_PSN_ID IS NULL;
中间的select 语句的WHERE 条件怎么写在最外层啊也就是说我想写成这样的
UPDATE TESTMDM M SET ( M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12,
                       M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23,
                       M.FC24,M.DATASOURCE_FROM)=(SELECT F.EMPLOYEENO,F.EMPLOYEENO,
                                                      F.EMPNAME,F.GENDER,F.BIRTHDAY,
                                                      F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME,
                                                      F.RESI_TYPE_CODE,F.MAR_STATUS_CODE,
                                                      F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE,
                                                      F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL,
                                                      F.CHARGE_RATE,F.COMM_CODE,'FC'
                                                   FROM FC_PSN F WHERE IS_MORE IS NULL )
WHERE  F.EMPNAME=M.NAME_CH AND DECODE(F.GENDER,'男','1','女','2')=M.SEX
       AND F.BIRTHDAY=M.BIRTHDATE
       AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL
这样的怎么实现啊,如果这样写在外层的话这里的这个别名F怎样处理啊

Answer 1

UPDATE TESTMDM M SET ( M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12, 
                      M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23, 
                      M.FC24,M.DATASOURCE_FROM)=(SELECT F.EMPLOYEENO,F.EMPLOYEENO, 
                                                      F.EMPNAME,F.GENDER,F.BIRTHDAY, 
                                                      F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME, 
                                                      F.RESI_TYPE_CODE,F.MAR_STATUS_CODE, 
                                                      F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE, 
                                                      F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL, 
                                                      F.CHARGE_RATE,F.COMM_CODE,'FC' 
                                                  FROM FC_PSN F WHERE IS_MORE IS NULL 
                                                    AND F.EMPNAME=M.NAME_CH 
                                                    AND DECODE(F.GENDER,'男','1','女','2')=M.SEX 
                                                    AND F.BIRTHDAY=M.BIRTHDATE 
                                                    AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL)  
          WHERE M.FC_PSN_ID IS NULL; 
中间的select 语句的WHERE 条件怎么写在最外层啊也就是说我想写成这样的 =====================================================
直接把where条件写在中间select语句的后面,也就是中间select 语句右括号的前面;

Answer 2

UPDATE TESTMDM M SET ( M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12, 
                      M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23, 
                      M.FC24,M.DATASOURCE_FROM)=(SELECT F.EMPLOYEENO,F.EMPLOYEENO, 
                                                      F.EMPNAME,F.GENDER,F.BIRTHDAY, 
                                                      F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME, 
                                                      F.RESI_TYPE_CODE,F.MAR_STATUS_CODE, 
                                                      F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE, 
                                                      F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL, 
                                                      F.CHARGE_RATE,F.COMM_CODE,'FC' 
                                                  FROM FC_PSN F WHERE IS_MORE IS NULL 
                                                    AND F.EMPNAME=M.NAME_CH 
                                                    AND DECODE(F.GENDER,'男','1','女','2')=M.SEX 
                                                    AND F.BIRTHDAY=M.BIRTHDATE 
                                                    AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL
WHERE ..............   --->WHERE条件在这里补充
)  
          WHERE M.FC_PSN_ID IS NULL; 

Answer 3

 update (select  M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12, 
                      M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23, 
                      M.FC24,M.DATASOURCE_FROM,
                     F.EMPLOYEENO,F.EMPLOYEENO, 
                     F.EMPNAME,F.GENDER,F.BIRTHDAY, 
                     F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME, 
                     F.RESI_TYPE_CODE,F.MAR_STATUS_CODE, 
                     F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE, 
                     F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL, 
                     F.CHARGE_RATE,F.COMM_CODE,'FC' t
          from TESTMDM M ,FC_PSN F
         WHERE IS_MORE IS NULL 
         AND F.EMPNAME=M.NAME_CH 
         AND DECODE(F.GENDER,'男','1','女','2')=M.SEX 
         AND F.BIRTHDAY=M.BIRTHDATE 
         AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL 
         AND M.FC_PSN_ID IS NULL 
          )
    set M.FC_PSN_ID = F.EMPLOYEENO,
        M.FC01=F.EMPLOYEENO, 
        ..............
        
  我更喜欢这样写，执行效率高。

Answer 4

update (select  M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12, 
                      M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23, 
                      M.FC24,M.DATASOURCE_FROM, 
                    F.EMPLOYEENO,F.EMPLOYEENO, 
                    F.EMPNAME,F.GENDER,F.BIRTHDAY, 
                    F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME, 
                    F.RESI_TYPE_CODE,F.MAR_STATUS_CODE, 
                    F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE, 
                    F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL, 
                    F.CHARGE_RATE,F.COMM_CODE,'FC' t 
          from TESTMDM M ,FC_PSN F 
        WHERE IS_MORE IS NULL and ...............)
set M.FC_PSN_ID = F.EMPLOYEENO,M.FC01=F.EMPLOYEENO,...........执行不了肯定是表有不规范数据

Answer 5

这是正确答案,哎还是自己的问题自己解决啊
UPDATE TESTMDM M SET ( M.FC_PSN_ID, M.FC01,M.FC02,M.FC04,M.FC05,M.FC06,M.FC08,M.FC09,M.FC12, 
                      M.FC13,M.FC17,M.FC18,M.FC19,M.FC20,M.FC21,M.FC22,M.FC23, 
                      M.FC24,M.DATASOURCE_FROM)=(SELECT F.EMPLOYEENO,F.EMPLOYEENO, 
                                                      F.EMPNAME,F.GENDER,F.BIRTHDAY, 
                                                      F.IDCARD_NO,F.ORG_CODE,F.ORG_NAME, 
                                                      F.RESI_TYPE_CODE,F.MAR_STATUS_CODE, 
                                                      F.EMP_TYPE,F.EMP_TYPE_CODE,F.ISSOLVE, 
                                                      F.ISRETIRE,F.EMP_ATTR_CODE,F.ISEMPDEAL, 
                                                      F.CHARGE_RATE,F.COMM_CODE,'FC' 
                                                  FROM FC_PSN F WHERE F.IS_MORE IS NULL 
                                                    AND F.EMPNAME=M.NAME_CH 
                                                    AND DECODE(F.GENDER,'男','1','女','2')=M.SEX 
                                                    AND F.BIRTHDAY=M.BIRTHDATE 
                                                    AND F.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL)
     WHERE EXISTS (SELECT F1.EMPLOYEENO, 
                          F1.EMPNAME,F1.GENDER,F1.BIRTHDAY, 
                          F1.IDCARD_NO,F1.ORG_CODE,F1.ORG_NAME, 
                          F1.RESI_TYPE_CODE,F1.MAR_STATUS_CODE, 
                          F1.EMP_TYPE,F1.EMP_TYPE_CODE,F1.ISSOLVE, 
                          F1.ISRETIRE,F1.EMP_ATTR_CODE,F1.ISEMPDEAL, 
                          F1.CHARGE_RATE,F1.COMM_CODE
                      FROM FC_PSN F1 WHERE F1.IS_MORE IS NULL 
                                                    AND F1.EMPNAME=M.NAME_CH 
                                                    AND DECODE(F1.GENDER,'男','1','女','2')=M.SEX 
                                                    AND F1.BIRTHDAY=M.BIRTHDATE 
                                                    AND F1.IDCARD_NO=M.IDCARD AND M.ROWS_MORE IS NULL AND M.FC_PSN_ID IS NULL)