表A如下:
COLUMN1 COLUMN2
n1 desc1
n1 null
n2 desc2
n3 desc3
n3 null
现在想根据COLUMN1中的值用COLUMN2中非空的更新是null的值,语句如下: update A SET A.COLUMN2=B.COLUMN2 FROM (SELECT * FROM A WHERE COLUMN2 IS NOT NULL) B WHERE A.COLUMN1=B.COLUMN1 AND A.COLUMN2 IS NULL
运行后提示ORA-00933: SQL 命令未正确结束,并定位到from处,这个语句在sql server上是没有问题的,不知oracle中有哪些不同的。
请各位高手看看是哪里错了,该怎么修改,谢谢!
COLUMN1 COLUMN2
n1 desc1
n1 null
n2 desc2
n3 desc3
n3 null
现在想根据COLUMN1中的值用COLUMN2中非空的更新是null的值,语句如下: update A SET A.COLUMN2=B.COLUMN2 FROM (SELECT * FROM A WHERE COLUMN2 IS NOT NULL) B WHERE A.COLUMN1=B.COLUMN1 AND A.COLUMN2 IS NULL
运行后提示ORA-00933: SQL 命令未正确结束,并定位到from处,这个语句在sql server上是没有问题的,不知oracle中有哪些不同的。
请各位高手看看是哪里错了,该怎么修改,谢谢!
set column2=(select column2 from a T2 where T1.column1=T2.column2 and column2 is not null)
where T1.column2 is null
;
-------------------- --------------------
n1 desc1
n1
n2 desc2
n3 desc3
n3SQL> update a T1
2 set column2=(select column2 from a T2 where T1.column1=T2.column1 and column2 is not null)
3 where T1.column2 is null
4 ; 已更新2行。SQL> select * from a;COLUMN1 COLUMN2
-------------------- --------------------
n1 desc1
n1 desc1
n2 desc2
n3 desc3
n3 desc3
这里才是完整的
SQL> create table A(column1 varchar2(20),column2 varchar2(20));表已创建。SQL> insert into A values('n1','desc1');已创建 1 行。SQL> insert into A values('n1',null);已创建 1 行。SQL> insert into A values('n2','desc2');已创建 1 行。SQL> insert into A values('n3','desc3'); 已创建 1 行。SQL> insert into A values('n3',null); 已创建 1 行。SQL> commit;提交完成。SQL> select * from a;COLUMN1 COLUMN2
-------------------- --------------------
n1 desc1
n1
n2 desc2
n3 desc3
n3SQL> update a T1
2 set column2=(select column2 from a T2 where T1.column1=T2.column1 and column2 is not null)
3 where T1.column2 is null
4 ; 已更新2行。SQL> select * from a;COLUMN1 COLUMN2
-------------------- --------------------
n1 desc1
n1 desc1
n2 desc2
n3 desc3
n3 desc3
请问有没有更好的方法呢?
还有为什么我那个语句不行呢?
set (column2,column3,column4...)=(select column2,column3,column4... from a T2 where T1.column1=T2.column1 and column2 is not null)
where T1.column2 is null ;
set COLUMN2 = (select COLUMN2 from tb where COLUMN1 = t.COLUMN1 and COLUMN2 is not null)
from tb tupdate tb
set COLUMN2 = (case when column2 is null then (select COLUMN2 from tb where COLUMN1 = t.COLUMN1 and COLUMN2 is not null) else column2 end)
from tb t
还有个问题大家还没解答呢,就是下面这句为什么不行呢?这个在sql server上是没有问题的,下面将查询的结果给个别名B当成一张表使用在oracle中是否有问题呢?
update A SET A.COLUMN2=B.COLUMN2 FROM (SELECT * FROM A WHERE COLUMN2 IS NOT NULL) B WHERE A.COLUMN1=B.COLUMN1 AND A.COLUMN2 IS NULL
你这种写法是在SQL SERVER中可以应用。
请问当select column2 from a T2 where T1.column1=T2.column2 and column2 is not null搜索出来有多行该怎么处理呢?能否指定取结果集中的第一行或者是第二行呢?
2、rownum = 1
这样要对对a表的column1和column2建立索引,索引可以提高速度好多的create index idx_tb on a表名 (column1,column2)如果还是慢,那么需要增大回滚段的容量;或者分批提交
set a.column2=(select b.column2 from tb b where a.column1=b.column2 and column2 is not null)
ta.column2 is null) where exists (select 1 from b.column2 from tb b where a.column1=b.column2 and column2 is not null);
1. v$session_wait, v$system_event, $session_event.
2. v$sql, check those with most disk gets, buffer gets. etc.
3. check the system cpu, io.
查找最耗费系统资源的SQL
--CPU
select b.sql_text,
a.buffer_gets,
a.executions,
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.buffer_gets desc , b.piece
; --IO
select b.sql_text,
a.disk_reads,
a.executions,
a.disk_reads/decode(a.executions , 0 , 1 , a.executions),
c.username
from v$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.disk_reads desc , b.piece
;
select s.sid,s.value "CPU Used"
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;
能知道哪个sid最消耗资源,前提,timed_statistics=true
然后从v$sqlarea能找到这个sql在干吗
SET COLUMN2 = (SELECT COLUMN2
FROM TAB B
WHERE A.COLUMN1 = B.COLUMN1
AND B.COLUMN2 IS NOT NULL
AND ROWNUM = 1)
WHERE EXISTS (SELECT 1
FROM TAB B
WHERE A.COLUMN1 = B.COLUMN1
AND B.COLUMN2 IS NOT NULL)--如果:
COLUMN1 COLUMN2
1 N1 DESC1
2 N1 DESC1
3 N2 DESC2
4 N3 DESC3
5 N3 DESC3
如有下面数据:
n3 desc3
n3 desc3
n3 null
declare
cursor c is select col1, col2, col3 from T1 where T1.col2 is null;
type t_t1 is table of c%rowtype;
t_t1_array t_t1;
begin
open c;
loop
fecth c bulk into t_t1_array limit 2000;
forall i in t_t1_array.first .. t_t1_array.last
update T1
set (col2, col3) = (select col2, col3 from T2 where T1.col1 = T2.col1 and T2.col2 is not null)
where T1.col2 is null
and T1.col1 = t_t1_array(i).col1 ;
exit when c%notfound;
end loop;
close c;
commit;
exception
when others then
rollback;
end;
cursor c is select col1 from T1 where T1.col2 is null;
type t_t1 is table of t1.col1%type;
t_t1_array t_t1;
begin
open c;
loop
fecth c bulk collect into t_t1_array limit 2000;
forall i in t_t1_array.first .. t_t1_array.last
update T1
set (col2, col3) = (select col2, col3 from T2 where T1.col1 = T2.col1 and T2.col2 is not null)
where T1.col2 is null
and T1.col1 = t_t1_array(i) ;
exit when c%notfound;
end loop;
close c;
commit;
exception
when others then
rollback;
end;