转:ORACLE 多表关联 UPDATE 语句

转:ORACLE 多表关联 UPDATE 语句

原帖：http://www.cnblogs.com/miley/archive/2010/04/15/1712617.html

 

 

 

  为了 方便起见 ,建立了以下简单模型 ,和构造了部分测试数据 :

   在某个业 务受理子系统 BSS中，

  --客户资 料表

  create table customers

   (

   customer_id   number(8)    not null,  -- 客户标示

   city_name     varchar2(10) not null,  -- 所在城市

   customer_type char(2)      not null,  -- 客户类型

 

   ...

   )

  create unique index PK_customers on customers (customer_id)

   由于某些 原因，客户所在城市这个信息并不什么准确，但是在

   客户服务 部的 CRM子系统中，通过主动服务获取了部分 客户 20%的所在

   城市等准 确信息，于是你将该部分信息提取至一张临时表中：

  create table tmp_cust_city

   (

   customer_id     number(8) not null,

   citye_name     varchar2(10) not null，

   customer_type  char(2)   not null

  )

 

 

1) 最简单的 形式

   --经确 认 customers表中所有 customer_id小于 1000均为 '北京 '

   --1000以内的均是公司走向全国之前的本城市的老客户 :)

   update customers

   set    city_name='北京 '

   where  customer_id<1000

 

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

   --这次 提取的数据都是 VIP，且包括新增的 ,所以顺便更新客户类别

   update customers   a       -- 使用别名

   set    customer_type='01' --01 为 vip， 00为普通

   where  exists (select 1

                  from   tmp_cust_city b

                  where  b.customer_id=a.customer_id

                 )

 

3) 两表 (多表 )关联 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

                 )

   注意在这 个语句中，

                                   =(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表进行全表

   更新，但 由于 (select b.city_name from tmp_cust_city b where where  b.customer_id=a.customer_id)

   有可能不 能提供 "足够多 "值，因为 tmp_cust_city只是一部分客户的信息，

   所以报错 （如果指定的列 --city_name可以为 NULL则另当别论）：

 

01407, 00000, "cannot update (%s) to NULL"

// *Cause:

// *Action:

 

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

   update customers a   -- 使用别名

   set    city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)

   或者

   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带来更好的性能：

 

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;

 

5) 关联 update的一个特例以及性能再探讨

   在 oracle的 update语句语法中，除了可以 update表之外，也可以是视图，所以有以下 1个特例：

    update (select a.city_name,b.city_name as new_name

            from   customers a,

                   tmp_cust_city b

            where  b.customer_id=a.customer_id

           )

    set    city_name=new_name

     这样 能避免对 B表或其索引的 2次扫描，但前提是 A(customer_id) b(customer_id)必需是 unique index

     或 primary key。否则报错：

 

01779, 00000, "cannot modify a column which maps to a non key-preserved table"

// *Cause: An attempt was made to insert or update columns of a join view which

//         map to a non-key-preserved table.

// *Action: Modify the underlying base tables directly.

 

6） oracle另一个常见错误

   回到 3)情况 ,由于某些原因， tmp_cust_city customer_id 不是唯一 index/primary key

   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

                 )

   当对于一 个给定的 a.customer_id

   (select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

   返回多余 1条的情况，则会报如下错误：

 

01427, 00000, "single-row subquery returns more than one row"

// *Cause:

// *Action:

 

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

   update customers a   -- 使用别名

   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 字句查看重复的纪录

   (select b.customer_id,b.city_name,count(*)

    from tmp_cust_city b,customers a

    where b.customer_id=a.customer_id

    group by b.customer_id,b.city_name

    having count(*)>=2

   )

原谅我当天不懂得珍惜 只知任性 坏事情唯愿你此刻可于虚空中 将心聆听将来若真的再有个约会会完成 真的会再有这样深情我以天为证跟你带领 我以天为证请你带领!!!---我和博友有个约会主题歌