to fibbery(惊魂一梦,飞上云霄): 那应该怎么写呢?不用一条sql也行,只要能达到我的目的。谢谢!
但一定要保证子查询中取得的记录对于被更新表中的数据是唯一的。否则,update失败。
因为a表是个历史记录表,我不能保证a表中的所有id在b表中都能找到。
例如:tab1是子查询表,tab2为被更新表 tab1: tab2: id a id a 1 a1 1 2 a2 2 3 a3 这样可以成功 如果: tab1: tab2: id a id a 1 a1 1 2 a2 2 2 a22 则不可以。
是的: ORA-01562 failed to extend rollback segment number string Cause: Failure occurred when trying to extend rollback segment. Action: This is normally followed by another error message that caused the failure. You may take the rollback segment offline to perform maintenance. Use the ALTER ROLLBACK SEGMENT OFFLINE command to take the rollback segment offline.ORA-01628 max # extents (string) reached for rollback segment string Cause: An attempt was made to extend a rollback segment that was already at the MAXEXENTS value. Action: If the value of the MAXEXTENTS storage parameter is less than the maximum allowed by the system, raise this value.记录太多,回滚段已经到达最大值。两种方法,一种是offline回滚段,一种是增加回滚段空间(或者回滚段数量)。
ORA-01562 failed to extend rollback segment number stringCause: Failure occurred when trying to extend rollback segment.Action: This is normally followed by another error message that caused the failure. You may take the rollback segment offline to perform maintenance. Use SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE SEGMENT_ID=string (where string is the segment number from the message) to determine the rollback segment name. Then use the ALTER ROLLBACK SEGMENT OFFLINE command to take the rollback segment offline. ORA-01628 max # extents (string) reached for rollback segment stringCause: An attempt was made to extend a rollback segment that was already at the MAXEXENTS value.Action: If the value of the MAXEXTENTS storage parameter is less than the maximum allowed by the system, raise this value. 可能是错误太多,回滚段放不下了。 先暂时在前端用pb的datawindow来实现了。
set a.huh = (select b.huh from b where a.id(+) = b.id);
update tab2,tab1
set tab2.a=tab1.a
where tab1.id=tab2.id;
你的方法也不行,错误号是1705:无法在关联列中指定外部连接。
oracle中update后面好像只能有一个表名,所以我用这种写法to zzok1():
b表中的id是唯一关键字
在select语句中检索出来的肯定是许多条记录,让一个字段去等于一大堆数据,我不太明白。
那应该怎么写呢?不用一条sql也行,只要能达到我的目的。谢谢!
tab1: tab2:
id a id a
1 a1 1
2 a2 2
3 a3
这样可以成功
如果:
tab1: tab2:
id a id a
1 a1 1
2 a2 2
2 a22
则不可以。
ORA-01562 failed to extend rollback segment number string
Cause: Failure occurred when trying to extend rollback segment. Action: This is normally followed by another error message that caused the failure. You may take the rollback segment offline to perform maintenance. Use the ALTER ROLLBACK SEGMENT OFFLINE command to take the rollback segment offline.ORA-01628 max # extents (string) reached for rollback segment string
Cause: An attempt was made to extend a rollback segment that was already at the MAXEXENTS value. Action: If the value of the MAXEXTENTS storage parameter is less than the maximum allowed by the system, raise this value.记录太多,回滚段已经到达最大值。两种方法,一种是offline回滚段,一种是增加回滚段空间(或者回滚段数量)。
可能是错误太多,回滚段放不下了。
先暂时在前端用pb的datawindow来实现了。