首页 > 数据库 >ORACLE 多表关联 UPDATE 语句

ORACLE 多表关联 UPDATE 语句

时间:2023-09-21 15:17:23浏览次数:82  
标签:customer city 多表 name -- UPDATE ORACLE where id


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



SQL 代码

1. --客户资料表
2. createtable
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



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


SQL 代码

1. createtable
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
4. set city_name='北京'
5. where


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



SQL 代码


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



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



SQL 代码

1. update customers a -- 使用别名
2. set city_name=(select b.city_name from tmp_cust_city b where
3. where exists (select
4. from
5. where
6. ) 
7. -- update 超过2个值
8. update customers a -- 使用别名
9. set (city_name,customer_type)=(select
10. from
11. where
12. where exists (select
13. from
14. where
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



有可能不能提供"足够多"值,因为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

或者



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. 
3. declare
4. cursor city_cur is
5. select
6. from
7. orderby
8. begin
9. for my_cur in
10. 
11. update
12. set
13. where
14. 
15. /** 此处也可以单条/分批次提交,避免锁表情况 **/ 
16. -- if mod(city_cur%rowcount,10000)=0 then
17. -- dbms_output.put_line('----');
18. -- commit;
19. -- end if;
20. end
21. end;


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



SQL 代码

1. update (select a.city_name,b.city_name as
2. from
3. tmp_cust_city b 
4. where
5. ) 
6. set


这样能避免对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
3. // map to a non-key-preserved table. 
4. // *Action: Modify


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
3. where exists (select
4. from
5. where
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


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



SQL 代码

1. (select b.customer_id,b.city_name,count(*) 
2. from
3. where
4. groupby
5. havingcount(*)>=2 
6. )

标签:customer,city,多表,name,--,UPDATE,ORACLE,where,id
From: https://blog.51cto.com/u_16230604/7554585

相关文章

  • oracle 小数格式化为百分数
    以下是从JAVAEYE中找到的代码,用途是将小数转换为百分数形式:SelectCONCAT(TO_CHAR('0.001'*100,'990.99'),'%')FROMDUAL;除此之外,还找到了其余的转换,放到这里留作日后之用:selectto_char(1234567.89,'9,999,999.99')idfromdual;selectto_char(1234567.89,'99......
  • Oracle查询表占用空间大小
    1\https://blog.csdn.net/zh_1721342390/article/details/130942912一、Oracle查询表空间占用情况Oracle是一种经典的关系型数据库管理系统,对于表占用空间大小的查询是数据库管理中的基本操作。查询表空间占用情况可以通过以下代码实现:SELECTa.tablespace_name"表空间名",  ......
  • oracle存储过程
    1.基本结构CREATEORREPLACEPROCEDURE存储过程名字(参数1INNUMBER,参数2INNUMBER)IS变量1INTEGER:=0;变量2DATE;BEGINEND存储过程名字2.SELECTINTOSTATEMENT将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条记录,否则抛出......
  • oracle 数据库对象自动备份
         本文介绍一种数据库对象备份的方法。其原因是因为我在开发时有两个数据库用户,他们的数据表结构相同,所开发的存储过程也相同,但是数据却是不同地方的数据。因为懒惰所以在修改过程包时只想修改一次就ok,所以想了一个半自动的办法,让数据库自动备份数据库对象,然后编写bat文件......
  • oracle 内置函数备忘录
    这两天在写oracle包,蛮大的一个,为了方便查找,就丢上来个。黄色是经常用到的,顺便看看对大家有没有帮助。  SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL>selectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;       A    ......
  • 使用 sudo apt-get update 命令时出现 Certificate verification failed: The certifi
    命令:sudoapt-getupdate问题:Certificateverificationfailed:ThecertificateisNOTtrusted. Tryinstallingca-certificates.忽略:1https://professional-store-packages.chinauos.com/appstoreeagleInRelease错误:3https://professional-store-packages.china......
  • Oracle 数据库11g版本dataguard创建的简单方法
    作者:ArupNanda DataGuard了解ActiveDataGuard如何通过实时查询,同时应用归档的的日志、将物理备用数据库转换为快照备用数据库以及对基础架构的一系列改进措施,让您对备份环境的投资物有所值。下载Oracle数据库11gOracle数据库11g对DataGuard功能进行了多方面的增强,......
  • greenplum到oracle的dbi_link的配置、调试与测试
    为了解决greenplum到oracle的数据库的数据互联互通的问题,特提供的dbi_link做了研究与测试,dbi_link的基本原理是用Perl的DBI和相应数据库的DBD来访问异构数据库,实现数据的互访与数据传递,这次研究的目的是想解决oracle数据仓库到greenplum的数据传输的问题,这次的研究大概可以分为以......
  • Oracle 数据库日常维护(3)
    常用DBA管理脚本一、数据库构架体系1、表空间的监控是一个重要的任务,我们必须时刻关心表空间的设置,是否满足现在应用的需求,以下的语句可以查询到表空间的详细信息SELECTTABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTE......
  • 【引用】Oracle全文检索方面的研究(全5)
    3.5Storage属性Oracle全文检索通常会生成一系列的辅助表,生成规则是dr$+索引名+$+表用途标识,由于这些表是oracle自动生成的,通常没有办法为这些表指定存储空间。为构造text索引所生成的辅助表指定表空间、存储参数(usethestoragepreferencetospecifytablespaceandcreation......