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

usernamezero 2009-03-09 05:46:30
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怎样处理啊
...全文
242 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
fzzlz 2009-03-10
  • 打赏
  • 举报
回复
应该是表的数据存在异常,应该用手工都不能操作。这个问题也遇见过,只有查一下所有表的数据
jdsnhan 2009-03-10
  • 打赏
  • 举报
回复
根据具体的提示找错误了
usernamezero 2009-03-10
  • 打赏
  • 举报
回复
这是正确答案,哎还是自己的问题自己解决啊
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)

usernamezero 2009-03-10
  • 打赏
  • 举报
回复
3楼的这个可以这样用吗?怎么没见过有这样的语法格式啊
xanaduwpz 2009-03-09
  • 打赏
  • 举报
回复
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,...........

执行不了肯定是表有不规范数据
jdsnhan 2009-03-09
  • 打赏
  • 举报
回复
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,
..............

我更喜欢这样写,执行效率高。
Andy__Huang 2009-03-09
  • 打赏
  • 举报
回复
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;
Andy__Huang 2009-03-09
  • 打赏
  • 举报
回复
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 语句右括号的前面;

3,494

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧