update 多表关联问题: ;
--例子: update 多表关联问题: ;
drop table temp1;
drop table temp2;
create table temp1(f1 number not null,f2 number not null,f3 varchar2(20) not null,f4 varchar2(20) not null,f5 varchar2(1) not null);
insert into temp1(f1,f2,f3,f4,f5) values(1,11,'NO.1','S1','A');
insert into temp1(f1,f2,f3,f4,f5) values(2,22,'NO.2','S2','A');
insert into temp1(f1,f2,f3,f4,f5) values(3,33,'NO.2','S2','B');
create table temp2(f1 number not null,f2 number not null,f3 varchar2(20) not null,f4 varchar2(20) not null,f5 varchar2(1) not null);
insert into temp2(f1,f2,f3,f4,f5) values(1,11,'New.1','Go1','A');
insert into temp2(f1,f2,f3,f4,f5) values(2,22,'New.2','Go2','A');
insert into temp2(f1,f2,f3,f4,f5) values(3,33,'New.3','Go3','A');
select * from temp1;
select * from temp2;
update temp1 t1
set t1.f3=(select f3 from temp2 t2 where t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f5=t1.f5),
t1.f4=(select f4 from temp2 t2 where t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f5=t1.f5);
最后出现:ORA-01407: 无法更新 ("APPS"."TEMP1"."F3") 为 NULL ,
为什么?
当上面的f3定义成可以为空时就是能通过。
在SQL Server2000中就不会出现这种错误。SQL Server2000的语法是:
update temp1 t1
set t1.f3=t2.f3,
t1.f4=t2.f4
from temp1 t1,temp2 t2
where t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f5=t1.f5