网易 
新闻 LOFTER 邮箱 相册 阅读 图书 有道 摄影 企业邮箱 优惠券 云笔记 闪电邮 邮箱大师 印像派 考拉海购
 
更多
博客 
手机博客 博客搬家 博客VIP服务 发现 小组 风格
 
群博客 博客油菜地 博客话题 博客热点 博客圈子 找朋友
 
登录  
 加关注
   显示下一条  |  关闭
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！立即重新绑定新浪微博》  |  关闭

樱之花

叶散的时候,你明白欢聚;花谢的时候,你明白青春.

导航

 
 
 
 
 

日志

 
 
关于我
yinzhihua2008

分类中“我的实验室”是我在日常工作中的一些知识总结,有些写的比较匆忙,可能大家在阅读时会产生困扰,后期有时间我会重新整理编辑,谢谢大家的到访,您们的支持是我前进的动力！

文章分类

ORACLE 多表关联 UPDATE 语句   

2009-12-08 16:43:46|  分类： ORACLE |  标签： |举报 |字号大中小 订阅

  下载LOFTER 我的照片书  |

为了方便起见,建立了以下简单模型,和构造了部分测试数据:
在某个业务受理子系统BSS中，

SQL 代码
  1. --客户资料表
  2. create table customers
  3. (
  4. customer_id number(8) not null, -- 客户标示
  5. city_name varchar2(10) not null, -- 所在城市
  6. customer_type char(2) not null, -- 客户类型
  7. ...
  8. )
  9. create unique index PK_customers on customers (customer_id)

由于某些原因，客户所在城市这个信息并不什么准确，但是在
客户服务部的CRM子系统中，通过主动服务获取了部分客户20%的所在
城市等准确信息，于是你将该部分信息提取至一张临时表中：

SQL 代码
  1. create table tmp_cust_city
  2. (
  3. customer_id number(8) not null,
  4. citye_name varchar2(10) not null，
  5. customer_type char(2) not null
  6. )

1) 最简单的形式

SQL 代码
  1. --经确认customers表中所有customer_id小于1000均为'北京'
  2. --1000以内的均是公司走向全国之前的本城市的老客户:)
  3. update customers
  4. set city_name='北京'
  5. where customer_id<1000

2) 两表(多表)关联update -- 仅在where字句中的连接

SQL 代码
  1. --这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别
  2. update customers a -- 使用别名
  3. set customer_type='01' --01 为vip，00为普通
  4. where exists (select 1
  5. from tmp_cust_city b
  6. where b.customer_id=a.customer_id
  7. )

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

SQL 代码
  1. update customers a -- 使用别名
  2. set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
  3. where exists (select 1
  4. from tmp_cust_city b
  5. where b.customer_id=a.customer_id
  6. )
  7. -- update 超过2个值
  8. update customers a -- 使用别名
  9. set (city_name,customer_type)=(select b.city_name,b.customer_type
  10. from tmp_cust_city b
  11. where b.customer_id=a.customer_id)
  12. where exists (select 1
  13. from tmp_cust_city b
  14. where b.customer_id=a.customer_id
  15. )

注意在这个语句中，
=(select b.city_name,b.customer_type from tmp_cust_city b
where b.customer_id=a.customer_id )
与
(select 1 from tmp_cust_city b
where b.customer_id=a.customer_id)
是两个独立的子查询，查看执行计划可知，对b表/索引扫描了2篇；
如果舍弃where条件，则默认对A表进行全表
更新，但由于

SQL 代码
  1. select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id

有可能不能提供"足够多"值，因为tmp_cust_city只是一部分客户的信息，所以报错（如果指定的列--city_name可以为NULL则另当别论）：

SQL 代码
  1. 01407, 00000, "cannot update (%s) to NULL"
  2. // *Cause:
  3. // *Action:

一个替代的方法可以采用：

SQL 代码
  1. update customers a -- 使用别名
  2. set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)

或者

SQL 代码
  1. set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),'未知')


-- 当然这不符合业务逻辑了

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

SQL 代码
  1. set serveroutput on
  3. declare
  4. cursor city_cur is
  5. select customer_id,city_name
  6. from tmp_cust_city
  7. order by customer_id;
  8. begin
  9. for my_cur in city_cur loop
  11. update customers
  12. set city_name=my_cur.city_name
  13. where customer_id=my_cur.customer_id;
  15. /** 此处也可以单条/分批次提交，避免锁表情况 **/
  16. -- if mod(city_cur%rowcount,10000)=0 then
  17. -- dbms_output.put_line('----');
  18. -- commit;
  19. -- end if;
  20. end loop;
  21. end;


5) 关联update的一个特例以及性能再探讨
在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例：

SQL 代码
  1. update (select a.city_name,b.city_name as new_name
  2. from customers a,
  3. tmp_cust_city b
  4. where b.customer_id=a.customer_id
  5. )
  6. set city_name=new_name


这样能避免对B表或其索引的2次扫描，但前提是 A(customer_id) b(customer_id)必需是unique index或primary key。否则报错：

SQL 代码
  1. 01779, 00000, "cannot modify a column which maps to a non key-preserved table"
  2. // *Cause: An attempt was made to insert or update columns of a join view which
  3. // map to a non-key-preserved table.
  4. // *Action: Modify the underlying base tables directly.


6）oracle另一个常见错误
回到3)情况,由于某些原因，tmp_cust_city customer_id 不是唯一index/primary key

SQL 代码
  1. update customers a -- 使用别名
  2. set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
  3. where exists (select 1
  4. from tmp_cust_city b
  5. where b.customer_id=a.customer_id
  6. )

当对于一个给定的a.customer_id
(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
返回多余1条的情况，则会报如下错误：

SQL 代码
  1. 01427, 00000, "single-row subquery returns more than one row"
  2. // *Cause:
  3. // *Action:

一个比较简单近似于不负责任的做法是

SQL 代码
  1. update customers a -- 使用别名
  2. set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id and rownum=1)

如何理解 01427 错误，在一个很复杂的多表连接update的语句，经常因考虑不周，出现这个错误，
仍已上述例子来描述，一个比较简便的方法就是将A表代入 值表达式 中,使用group by 和
having 字句查看重复的纪录

SQL 代码
  1. (select b.customer_id,b.city_name,count(*)
  2. from tmp_cust_city b,customers a
  3. where b.customer_id=a.customer_id
  4. group by b.customer_id,b.city_name
  5. having count(*)>=2
  6. )
  评论这张
 
阅读(1451)| 评论(0)

历史上的今天

评论

<#--最新日志，群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇，下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

我的照片书 - 手机博客 - 下载LOFTER APP - 订阅此博客

网易公司版权所有 ©1997-2018