oracle update 博客分类： oracle oracle update

 update a set a.col1 = (select b.col1  from b  where b.col2 = a.col2)
 where exists
 (select * from b where a.col2 = b.col2)

/**oracle与sqlserver更新update的用法有不同，oracle是怎么处理的呢
update a set a.col1=100     将所有行全部的特定列col1更新为特定值
update a set a.col1=100 where a.col2<10 将满足col2条件的行的col1列的值更新为特定的值
update a set a.col1=a.col1+a.col2 where a.col2<10 同一个表中的简单计算更新
update a set a.col1=(select b.col1 from b where a.col2=b.col2)
where exists(select * from b where a.col2=b.col2) 级联更新,将满足a.col2=b.col2的行的a.col1更新为对应的
b.col1的值。当且仅当a=b时可以将where条件去掉。这个更新还可以这样理解：
update a set a.col1=(select b.col1 from b where a.col2=b.col2)表示对于a中所有行满足a.col2=b.col2
的进行更新，不满足条件的也更新，只不过找不到对应的值，只能将空值赋之，如果此时a.col1不允许为空那么会报插入空值错误。
所以只有加上where条件，才能将a.col2<>b.col2的那些在a中的数据得以幸存（不被更新为空）。

 

两表(多表)关联update -- 被修改值由另一个表运算而来

 

update customers a -- 使用别名
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
-- update 超过2个值
update customers a -- 使用别名
set (city_name,customer_type)=(select b.city_name,b.customer_type
from tmp_cust_city b
where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)

 Explain Plan:

 


 


 
 
 

 

注意在这个语句中，
=(select b.city_name,b.customer_type from tmp_cust_city b
where b.customer_id=a.customer_id )//为来确定表b中用来作为目录数据的记录
与
(select 1 from tmp_cust_city b
where b.customer_id=a.customer_id)//为来确定表a中需要处理的记录
是两个独立的子查询，查看执行计划可知，对b表/索引扫描了2篇；
如果舍弃where条件，则默认对A表进行全表更新.

 

如果字段city_name,customer_type不能为null，则sql会执行失败，因因为tmp_cust_city只是一部分客户的信息，所以报错（如果指定的列--city_name可以为NULL则另当别论）：

01407, 00000, "cannot update (%s) to NULL"
// *Cause:
// *Action:

 关于性能：

上述在一些情况下，因为B表的纪录只有A表的20-30%的纪录数，
考虑A表使用INDEX的情况，使用cursor也许会比关联update带来更好的性能：

set serveroutput on
declare
cursor city_cur is
select customer_id,city_name
from tmp_cust_city
order by customer_id;
begin
for my_cur in city_cur loop
update customers
set city_name=my_cur.city_name
where customer_id=my_cur.customer_id;
/** 此处也可以单条/分批次提交，避免锁表情况 **/
-- if mod(city_cur%rowcount,10000)=0 then
-- dbms_output.put_line('----');
-- commit;
-- end if;
end loop;
end;

 

 

 

http://it.oyksoft.com/post/641/

 http://www.jb51.net/article/30465.htm