update 多表关联问题: ;

[afeisky]

--例子: 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

[beckhambobo]

update temp1 t1
set (t1.f3,t1.f4)=(select f3,f4 from temp2 t2 where t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f5=t1.f5);

[afeisky]

因为f3,f4都不可以null，所以beckhambobo(beckham) ( ) 方法是好的，但不行。
用游标就解决问题了。

[bzszp]

update temp1 t1
set (t1.f3,t1.f4)=(select nvl(min(f3),0),nvl(min(f4),0) from temp2 t2 where t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f5=t1.f5);

[Drate]

看一看ORACLE是怎么说的吧，。。。

ORA-01407 cannot update (string) to NULL

Cause: An attempt was made to update a table column "USER"."TABLE"."COLUMN" with a NULL value.

For example, if you enter:

connect scott/tiger
update table a (a1 number not null);
insert into a values (null);


Oracle returns:

ORA-01407 cannot update ("SCOTT"."A"."A1") to NULL


which means you cannot update the column "SCOTT"."A"."A1" to NULL.

Action: Retry the operation with a value other than NULL.

如果用上NVL函数的话，我想就应该不会有这个问题了吧。。

[chanet]

use nvl() function

[Philson]

哥们，你把insert into temp1(f1,f2,f3,f4,f5) values(3,33,'NO.2','S2','B')中的‘B’改成‘A’，就没问题了！如果你的update语句中的子查询没有返回一行，oracle就试图将该列更新为null，如果返回多行，呵呵，它又会报错啦！

[S海鸥]

update sql study

[Oracle10g]

初学 ORACLE 有很多地方不明白，请教个问题：
TableA 表的字段： Num Number(5),Name VarChar(20)

TableB 表的字段： Num Number(5),Name VarChar(20)，IsOld Number(1) Default 0

用 SQL 2000 的描述方法是：
Update B
set B.Name = A.Name,B.IsOld = 0
from TableA A,TableB B
where A.Num = B.Num and B.IsOld = 1;
在 ORACLE 中怎实现？