`

ORACLE 多表关联 UPDATE 语句

阅读更多

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

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

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

SQL 代码
  1. createtable tmp_cust_city
  2. (
  3. customer_id number(8) notnull,
  4. citye_name varchar2(10) notnull
  5. customer_type char(2) notnull
  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
  2. declare
  3. cursor city_cur is
  4. select customer_id,city_name
  5. from tmp_cust_city
  6. orderby customer_id;
  7. begin
  8. for my_cur in city_cur loop
  9. update customers
  10. set city_name=my_cur.city_name
  11. where customer_id=my_cur.customer_id;
  12. /** 此处也可以单条/分批次提交,避免锁表情况 **/
  13. -- if mod(city_cur%rowcount,10000)=0 then
  14. -- dbms_output.put_line('----');
  15. -- commit;
  16. -- end if;
  17. end loop;
  18. 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 toinsertorupdate columns of a joinview 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. groupby b.customer_id,b.city_name
  5. havingcount(*)>=2
  6. )
分享到:
评论

相关推荐

    ORACLE_多表关联_UPDATE_语句

    例如: 两表(多表)关联update -- 仅在where字句中的连接 --这次提取的数据都是VIP,且包括新增的,所以顺便更新客户类别 update customers a -- 使用别名 set customer_type='01' --01 为vip,00为普通 where ...

    ORACLE多表关联的update语句

    --客户资料表 create table customers ( customer_id number(8) not null, -- 客户标示 city_name varchar2(10) not null, -- 所在城市 customer_type char(2) not null, -- 客户类型 ... ) create ...

    Oracle merge合并更新函数实例详解

    通过MERGE语句,根据一张表或多表联合查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。通过...

    sql语句生成器+支持各大数据库+说明书

    SQL语句生成器的特色 支持几乎所有类型的数据库, 包括小型(桌面)数据库:Fox DBF、Microsoft Execl、Text、Borland ...支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句  附属工具内嵌入Delphi IDE

    oracle实验报告

    2、多表查询 四、 SQL*PLUS常用命令 表1 常用报表格式化名命令 命令 定义 Btitle 为报表的每一页设置底端标题 Column 设置列的标题和格式 Compute 让SQL*PLUS计算各种值 Remark 将某些字标记为注释 Set linesize ...

    oracle 数据按主键删除慢问题的解决方法

    问题描述: 根据表主键id删除一条数据,在PL/SQL上...sql语句来处理update和delete数据操作。但是依然没效果!!! 怀疑数据库出问题了!~  于是拿sql语句在PL/SQL客户端执行,查看执行计划。删除和更新都能使用到索引

    SQL语句生成及分析器

    该工具的主要特色: 1、支持几乎所有类型的数据库, ...6、支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 7、附属工具内嵌入Delphi IDE(支持Delphi 5和Delphi 6) 8、文件拖放(SQL和TXT文件)

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    其三、职业方向多:Oracle数据库管理方向、Oracle开发及系统架构方向、Oracle数据建模数据仓库等方向。 四、 如何学习 认真听课、多思考问题、多动手操作、有问题一定要问、多参与讨论、多帮组同学 五、 体系结构 ...

    SQL语句自动生成工具

    支持几乎所有类型的数据库, 包括小型(桌面)数据库:Fox DBF...对于Oracle 支持Union All,Minus,Intersect) &lt;br&gt;支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 附属工具内嵌入Delphi IDE

    SQL语句生成及分析器(中文绿色)

    1、支持绝大部分数据库,包括 大型数据库Oracle,Sybase(包括SQL AnyWhere),DB2,MS_SQL 中型数据库MS_Access,MySQL 桌面型数据库Paradox,DBF系列数据库... 10.4 简单SQL查询语句转换为Delete,Update,Insert语句

    Oracle触发器trigger详解

    每当一个特定的数据操作语句(insert update delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列。 举个简单的例子: 当员工表中新增一条记录后,自动打印“成功插入新员工” create or replace ...

    SQL语句自动生成器简体中文版

    支持几乎所有类型的数据库, 包括小型(桌面)数据库:Fox ...对于Oracle 支持Union All,Minus,Intersect) &lt;br&gt;支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 附属工具内嵌入Delphi IDE

    sql语句生成与分析器.rar

    11.4 简单SQL查询语句转换为Delete,Update,Insert语句 11.5 复制为字符串(支持对Java、C#、Delphi、VB、PowerBuilder开发语言的支持) 11.6 灵活的拖放功能 11.7 在线版本更新 11.8 查询结果输出为SQL脚本...

    OCPOCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053)--详细书签版(第2/2部分)

    CruiseYoung提供的带有详细书签的电子书籍目录 ... OCPOCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-... 12.1 使用同等联接和非同等联接编写SELECT语句访问多个表的数据 398 12.1.1 联接的类型 398 12.1.2 ...

    经典SQL语句大全

    10、说明:两张关联表,删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题: select * from a left ...

    MySQL笔试题一条语句解决问题多表联查聚合查询

    中等难度笔试题,包括select / insert / update / delete 语法...多表联合查询,排序、分组等功能运用 包括但不限于MySQL,也可推广至Oracle、DB2等常见关系型数据库 考验的包括基本的CRUD水平,以及边界情况的判断处理

    经典全面的SQL语句大全

     10、说明:两张关联表,删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )  11、说明:四表联查问题: select * from a left ...

    SQL语言生成器(delphi)

    1、支持几乎所有类型的数据库, 包括小型(桌面)数据库:Fox DBF、Microsoft Execl、Text、Borland Paradox、 ...4、支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 4、附属工具内嵌入Delphi IDE

Global site tag (gtag.js) - Google Analytics