sql*plus下呢?
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc from bfrb where RQ between '2003-01-01' and '2003-12-31' and bf=bfrbhz.bf order by rq desc) a
where rownum=1);
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc from bfrb where RQ between '2003-01-01' and '2003-12-31' and bf=bfrbhz.bf order by rq desc) a
where rownum=1);
解决方案 »
- oracle如何实现把一个数据表中的数据复制到备份数据表中
- Update语句怎样一次完成一一对应的数据更新???
- 这个查询怎么做???
- 机器换了ip后,执行conn /as sysdba时遇到ORA_01031错误
- Backup table以及权限,制约信息的确认!在线等……
- 本地数据库 忘了SID,不知道该怎么才能找到.
- varchar & varchar2有什么区别?
- 创建用户帐号的问题!----高手求救
- 数据库删除不了
- 修改最大连接无效
- varchar2类型在Oracle中理论上可以存储4000bytes的字节 , 但是我在某数据库中设定一个可以存储4000字节的字段, 在插入3千多个字节的时候
- 求救。关于12560错误
SQL> select 1 from dual where trunc(sysdate)='2003-07-30';
select 1 from dual where trunc(sysdate)='2003-07-30'
*
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
已用时间: 00: 00: 00.30
SQL> select 1 from dual where trunc(sysdate)='30-7月 -03'; 1
----------
1已用时间: 00: 00: 00.40
SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。已用时间: 00: 00: 00.20
SQL> select 1 from dual where trunc(sysdate)='2003-07-30'; 1
----------
1已用时间: 00: 00: 00.80
SQL>
SQL> select 1 from dual where trunc(sysdate)='2003-07-30';
select 1 from dual where trunc(sysdate)='2003-07-30'
*
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
已用时间: 00: 00: 00.30
SQL> select 1 from dual where trunc(sysdate)='30-7月 -03'; 1
----------
1已用时间: 00: 00: 00.40
SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。已用时间: 00: 00: 00.20
SQL> select 1 from dual where trunc(sysdate)='2003-07-30'; 1
----------
1已用时间: 00: 00: 00.80
SQL>
to bzszp(SongZip) :是不是应该是....sjkfc=(select a.sjkfc from .....
您好像少了一个from
如果改成这样,toad提示:ORA-00904: 无效列名
似乎ORACLE在此种情况下不允许用order by
不过就不能用rownum=1来取最大的一条了
这样就只能通过 rq =( select max(rq) from tbname where ...)麻烦~
update bfrbhz set sjkfc=(select nvl(sjkfc,0) from bfrb where rq=(select max(rq) from bfrb where trim(RQ) between '2003-01-01' and '2003-12-31' and trim(bf)=bfrbhz.bf and rownum=1) and trim(bf)=bfrbhz.bf)
但我觉得,在SQL Server很轻松实现的,但在ORACLE就得这么费周折?
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc,bf from bfrb where RQ between '2003-01-01' and '2003-12-31' order by rq desc) a where rownum=1 and a.bf=bfrbhz.bf);
或者
update bfrbhz set sjkfc=(select a.sjkfc from (select bf,sjkfc,rank() over(order by rq desc) rk from bfrb where RQ between '2003-01-01' and '2003-12-31') a where a.bf=bfrbhz.bf and rk=1)
这样两层子查询的话,会提示,无效列名的
8i
8.1.5
where a.bf=b.bf and
to_char(to_date(a.rq,'yyyymmdd'),'yyyymmdd') between '20030101' and '20031231' and
to_char(to_date(a.rq,'yyyymmdd'),'yyyymmdd')= (select to_char(to_date(max(rq),'yyyymmdd'),'yyyymmdd') from bfrb);
update bfrbhz set sjkfc=(select a.sjkfc from
(select sjkfc,bf from bfrb where RQ between '2003-01-01' and '2003-12-31' order by rq desc) a where rownum=1 and a.bf=bfrbhz.bf);
或者
update bfrbhz set sjkfc=(select a.sjkfc from (select bf,sjkfc,rank() over(order by rq desc) rk from bfrb where RQ between '2003-01-01' and '2003-12-31') a where a.bf=bfrbhz.bf and a.rk=1)
第一个OK,
第二个:ORA-00923: 未找到预期 FROM 关键字