FACT(CUSTOM3ID,CUSTOM4ID,DDATA)
86 1 719,682,889.03
86 3 719,682,889.03
86 5 719,682,889.03
86 64 719,682,889.03
86 150 719,682,889.03
87 1 719,682,889.03
87 3 719,682,889.03
87 5 719,682,889.03
87 64 719,682,889.03
87 150 719,682,889.03
共10条记录
Custom3(ID,Label)
285 PaidICDet05
300 PICRMBRate
301 PICRMBVa
298 PICHKDRate
299 PICHKDVa
281 PaidICDet01
......Custom4(ID,Label)
129 QRptAdjs
130 QRptAdjs01
131 QRptAdjs02
132 QRptAdjs03
133 QRptAdjs04
134 QRptAdjs05
........求教一:
我想新建一张表A2,将A1的记录放到A2中,但是将CUSTOM3ID,CUSTOM4ID换成与Custom3和Custom4相匹配的Label,结果还是10条记录。
求教二:
如果想直接将FACT表里的ID更新成对应表的Label,那么这个更新怎么谢。小弟叩谢!
86 1 719,682,889.03
86 3 719,682,889.03
86 5 719,682,889.03
86 64 719,682,889.03
86 150 719,682,889.03
87 1 719,682,889.03
87 3 719,682,889.03
87 5 719,682,889.03
87 64 719,682,889.03
87 150 719,682,889.03
共10条记录
Custom3(ID,Label)
285 PaidICDet05
300 PICRMBRate
301 PICRMBVa
298 PICHKDRate
299 PICHKDVa
281 PaidICDet01
......Custom4(ID,Label)
129 QRptAdjs
130 QRptAdjs01
131 QRptAdjs02
132 QRptAdjs03
133 QRptAdjs04
134 QRptAdjs05
........求教一:
我想新建一张表A2,将A1的记录放到A2中,但是将CUSTOM3ID,CUSTOM4ID换成与Custom3和Custom4相匹配的Label,结果还是10条记录。
求教二:
如果想直接将FACT表里的ID更新成对应表的Label,那么这个更新怎么谢。小弟叩谢!
insert into A2(CUSTOM3Label,CUSTOM4Label,DDATA)
select Custom3.Label,Custom4.Label,DDATA
from FACT,Custom3,Custom4
where CUSTOM3ID = Custom3.ID
and CUSTOM4ID = Custom4.ID;2.
update FACT
set CUSTOM3ID = (select Custom3.Label from Custom3,FACT where Custom3.ID = FACT.CUSTOM3ID),
CUSTOM4ID = (select Custom4.Label from Custom4,FACT where Custom4.ID = FACT.CUSTOM4ID);
UPDATE a1 SET scenarioid = (select s.label from cofco_scenario s,a1 where s.id=a1.scenarioid)
ORA-01427: 单行子查询返回多个行
不是10条记录?CUSTOM3.ID或CUSTOM4.ID是不是有重复题2:
update a1 set scenarioid=(select label from cofco_scenario where id=a1.scenarioid)
using cofco_scenario s
on a.scenarioid = s.id
when matched then
update set a.scenarioid = s.label;
select tt.a,tt.b,tt.c
from
(select Custom3.Label a,Custom4.Label b ,DDATA c,FACT.CUSTOM3ID d,FACT.CUSTOM4ID e,
Custom3.ID f,Custom4.ID g
from FACT,Custom3,Custom4
) tt
where tt.d=tt.f and tt.e = tt.g
update a1 set custom4id=(select label from cofco_custom4 where id=a1.custom4id)
ORA-01722: 无效数字请问这是为什么呀,谢谢
你的costom4id字段是数值型的,但是插入的值是字符,不能匹配
可以新建个字段,或者把costom4id字段全部清空,然后用alter table转换类型