如果没有找到值,你就把这个字段变成NULL了。要想达到目的,用NVL判断一下看看吧。
解决方案 »
- 查询sys.trigger$ 报错ORA-00932: 資料類型不一致
- 查看Oracle的连接数,以及每增加一个连接数,所消耗的CPU。
- 请教一个简单的问题,如何根据A表字段查询B表的字段信息,并把结果插入A表中。
- csdn的大侠们,帮忙看看,在线等~
- 在PL/SQL上创建用户时,出现了'无效的TEMPORARY表空间标识符'的错误
- exp导出时,出错!!(提示:SP2-0734: 未知的命令开头 "exp system..." - 忽略了剩余的行。)
- 关于备份的问题 ??
- 求助!!!!!!!!
- 关于OracleOraHome81ManagementServer服务无法启动问题?
- 这样的sql语句怎么处理比较好呢??
- 数据倒换问题
- 为什么要限制在2000个字节内???怎么提高字节数??
= ( select x,y,z
from t1
where t.x ==t1.x)
where exists
( select x,y,z
from t1
where t.x ==t1.x)[email protected]> update
2 ( select columnName, value -- 不能用 col 的别名 因为外部的看不到内部的view
3 from name, lookup -- 如 value v 下面的set columnName=v
4 where name.keyname = lookup.keyname
5 and lookup.otherColumn = :other_value )
6 set columnName = value
7 /
update scm.inventory_batch a
set a.invb_valid_date=(select nvl(min(b.date_ok),a.invb_valid_date)
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
group by b.spcode)
update scm.inventory_batch a
set a.invb_valid_date=(select min(b.date_ok)
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
group by b.spcode)
where exists (select 1
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num)
你的方法不行,还是被置空。
對於要UPDATE的表你一定要明確UPDATE所涉及的數據的條件。
更改如下:
update scm.inventory_batch a
set a.invb_valid_date=
(
select min(b.date_ok)
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
group by b.spcode
)
where (a.invb_pro_id,a.invb_batch_num) in
(
select distinct b.spcode,b.batch_num
from scm2004.batch_num b
)
set a.invb_valid_date=
(
select min(b.date_ok)
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
group by b.spcode
)
where exists
(
select 'x' from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
)
系统环境:
1、操作系统:Windows 2000 Server,机器内存128M
2、数据库: Oracle 8i R2 (8.1.6) for NT 企业版
3、安装路径:C:\ORACLE模拟现象: 可通过重建数据文件来恢复,前提是归档日志文件保存完整先将数据库设置为归档模式SQL*Plusconn system/manager--创建实验表空间
create tablespace test datafile
'c:\test.ora' size 5M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
default storage (initial 128K next 1M pctincrease 0)
/--创建实验用户
drop user test cascade;
create user test identified by test default tablespace test;
grant connect,resource to test;
conn test/testcreate table a(a number);
insert into a values(1);
insert into a select * from a; --反复插入,达到100万条
commit;--关闭数据库
SVRMGR> connect internal
SVRMGR> alter system switch logfile; --强制归档
SVRMGR> alter system switch logfile;
SVRMGR> alter system switch logfile;
SVRMGR> shutdown--操作系统下删除test.ora文件--重新启动数据库
SVRMGR> connect internal
SVRMGR> startup这时,可以mount上,但无法打开,因为数据文件test.ora不存在,
显示错误如下:ORA-01157: ????/?????? 8 - ??? DBWR ????
ORA-01110: ???? 8: 'C:\TEST.ORA'SVRMGR> connect internal
SVRMGR> startup mount
SVRMGR> alter database create datafile 'c:\test.ora';
SVRMGR> set autorecovery on
SVRMGR> recover datafile 'c:\test.ora';
SVRMGR> alter database open;conn test/test
select count(*) from a; --数据又恢复到100万条--删除实验表空间
conn system/manager
alter tablespace test offline;
drop tablespace test INCLUDING CONTENTS;
drop user test;
--如果是非归档模式,也可以运用以上方法,
--前提是:输入记录所占空间的大小不超过所有联机日志文件的大小
--即:用联机日志文件来恢复
set a.invb_valid_date=(select min(b.date_ok)
from scm2004.batch_num b
where a.invb_pro_id=b.spcode
and a.invb_batch_num=b.batch_num
group by b.spcode)
where (select count(1) from scm2004.batch_num c where a.invb_pro_id=c.spcode and a.invb_batch_num=c.batch_num) > 0