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);
看一看ORACLE是怎么说的吧,ORA-01407 cannot update (string) to NULLCause: 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函数的话,我想就应该不会有这个问题了吧。。
哥们,你把insert into temp1(f1,f2,f3,f4,f5) values(3,33,'NO.2','S2','B')中的‘B’改成‘A’,就没问题了!如果你的update语句中的子查询没有返回一行,oracle就试图将该列更新为null,如果返回多行,呵呵,它又会报错啦!
初学 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 中怎实现?
用游标就解决问题了。
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);
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函数的话,我想就应该不会有这个问题了吧。。
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 中怎实现?