ORACLE批量更新方法

转载 2013年12月03日 08:15:52

第一种方法 就是update tab a set a.name=(select b.name from b where a.id=b.id) where exists (select 1 from b where a.id=b.id)   但是我不知道为啥要加后面 exists 

sql%rowcount的数据量(不管你是否更新到)就是select count(*) from table_name的数据量啦,即全表更新
第二种就是 merge into tab
  using ()  on (条件)
  when match then
   do update
  when no match then
  do insert       

第三种估计好多人不知道    update(select a.name,b.name from a,b where a.id=b.id) set a.name=b.name;  但是这种情况下要保证b.id要唯一 最好还能建立索引啥的

第二种oracle merge into的使用

分类: SQL Programing 439人阅读 评论(0)收藏 举报

在做报表的时候,遇到这么一个问题,由于数据量大,数据上传时间问题,经常要处理更新几天前的数据,这时需要涉及UPDATE或者INSERT两个操作,这时推荐用MERGE INTO,但用这个时候需注意一个问题,我先来一个测试:


SQL> select * from ytrep.tab1;
 
COL_A      COL_B      COL_C
---------- ---------- ----------
1          A          
2          B          
3          C          
4          D          
5          E          
6          F          
7          G          
8          H    

      
 
8 rows selected


SQL> select * from ytrep.tab2;
 
COL_A      COL_B      COL_C      COL_D
---------- ---------- ---------- ----------
1          A          A1         1
1          A          A1         2

2          B          B1         1
3          C          C1         1
4          D          D1         1
5          E          E1         2
5          E          E2         1
6          F          F1         1
6          F          F1         2
9          G          G1         1
10         H          H1         1
 
11 rows selected

有以上两表,其中需要根据ytrep.tab2表的col_a及col_b值来更新ytrep.tab1的col_c值,这时,我们来执行以下SQL:

Merge into ytrep.tab1 p
using (
        select t.col_A, t.col_B, col_C
        from ytrep.tab2 t
      ) tmp
on (p.col_A= tmp.col_A and p.col_B= tmp.col_B)
  when not matched then
    insert (col_A, col_B, col_C) values (tmp.col_a, tmp.col_B, tmp.col_C)
  when matched then
    update set p.col_c = tmp.col_c;

提示:

ORA-30926: 无法在源表中获得一组稳定的行

ORA-30926: unable to get a stable set of rows in the source tables


好了,现在我们来分析为什么会出现这种错误,在ON条件里,我们可以看到p.col_A= tmp.col_A and p.col_B= tmp.col_B,

当tmp表返回记录后,ytrep.tab1取第一条记录 col_a = '1' and col_b = 'A' 与tmp表关联时,发现tmp有两条匹配的记录,这时ORACLE就无法判断到底UPDATE SET时应该取TMP表的哪条记录的COL_C值是A1还是A2了,这时ORACLE就返回以上错误;

这时,我们就可以推断,TMP结果集里返回的在ON条件里有涉及字段的值必须是唯一的,否则肯定报以上ORA-30926错误;


这时,有人也许会问,那到底该怎么写呢?

可分为两种情况:

1、一种是通过将TMP表的数据GROUP BY,取得MIN或MAX值;

Merge into ytrep.tab1 p
using (
        select t.col_A, t.col_B, max(col_C) col_C  --min(col_C) col_C
        from ytrep.tab2 t
        group by t.col_a, t.col_b
      ) tmp
on (p.col_A= tmp.col_A and p.col_B= tmp.col_B)
  when not matched then
    insert (col_A, col_B, col_C) values (tmp.col_a, tmp.col_B, tmp.col_C)
  when matched then
    update set p.col_c = tmp.col_c;


SQL> select * from ytrep.tab1;
 
COL_A      COL_B      COL_C
---------- ---------- ----------
1          A          A1
2          B          B1
3          C          C1
4          D          D1
5          E          E2
6          F          F1
7          G          
8          H          
10         H          H1
9          G          G1

10 rows selected


2、一种是分组后再根据某列值排序取第一行或最后一行值;

Merge into ytrep.tab1 p
using (
        select distinct t.col_A, t.col_B, first_value(col_c) over (partition by t.col_a, t.col_b order by t.col_d asc) col_c
        from ytrep.tab2 t
      ) tmp
on (p.col_A= tmp.col_A and p.col_B= tmp.col_B)
  when not matched then
    insert (col_A, col_B, col_C) values (tmp.col_a, tmp.col_B, tmp.col_C)
  when matched then
    update set p.col_c = tmp.col_c;


 
SQL> select * from ytrep.tab1;
 
COL_A      COL_B      COL_C
---------- ---------- ----------
1          A          A1
2          B          B1
3          C          C1
4          D          D1
5          E          E2
6          F          F1
7          G          
8          H          
10         H          H1
9          G          G1

10 rows selected


以上两个SQL,大家可以通过改变order by排序方式对比一下结果。


以上只是本人学习测试结果,如有不同意见或或好的建议,可以留言。谢谢!

 

 

 

ORACLE批量更新四种方法比较

 软件环境 Windows 2000 + ORACLE9i 硬件环境 CPU 1.8G + RAM 512M 现在我们有2张表 如下:T1--大表 10000笔 T1_FK_ID T2--小表 500...
  • huanghui22
  • huanghui22
  • 2006年10月05日 20:30
  • 53887

Oracle批量更新

①导出批量更新SQL select CONCAT(CONCAT(CONCAT(CONCAT('update test_a set a_date=', b_date), ...
  • kancy110
  • kancy110
  • 2017年06月28日 16:36
  • 196

oracle 批量更新四种方法比较

转ORACLE批量更新四种方法比较软件环境 Windows 2000 + ORACLE9i 硬件环境 CPU 1.8G + RAM 512M现在我们有2张表 如下: T1–大表 10000笔 T...
  • zsw12013
  • zsw12013
  • 2016年07月20日 11:31
  • 3499

Oracle的update语句优化研究 批量更新

Oracle的update语句优化研究 一、 update语句的语法与原理 1. 语法 单表:UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 如:update t_joi...
  • zhangbingtao2011
  • zhangbingtao2011
  • 2016年05月20日 21:34
  • 11841

Oracle批量更新,将一个表的数据批量更新另一表

【问题】现在有两个千万级别的结构相同数据不同数据表T_SMS_PHONENO(目的表),T_SMS_PHONENO2(源表),根据源表数据更新目的表的数据。 【分析】根据经验,更新方法一般有以下...
  • xiaoyangsavvy
  • xiaoyangsavvy
  • 2017年06月23日 15:01
  • 657

Oracle批量更新任务分解(数据量过亿)

--游标更新,批量提交 --设定一万条数据提交一次,耗时十多个小时未得到任何反馈,中断执行,重新分解 --维保信息(目标),T_BIZ_Temp,数据量7500万行 --维修工时(源表),T_MTD_...
  • guosyu
  • guosyu
  • 2017年10月12日 15:18
  • 335

Oracle 事务机制 批量添加,修改,更新

---------------------------------------------------------------------------------------------2011-12...
  • y112102
  • y112102
  • 2011年11月17日 16:51
  • 486

Oracle批量更新数据

 UPDATE WAR_DAYINVS A SET (A.IQTY,A.IAMT,A.PDATE) =                         (SELECT QTY,T_PRICE,TO_D...
  • wizardlun
  • wizardlun
  • 2010年03月30日 13:49
  • 2236

Oracle数据的批量更新

将同一表的同一字段进行相加 update AAA T1    set (T1.Age, T1.score) =    (select t2.age+t3.age, t2.score+t3.sc...
  • Draling
  • Draling
  • 2014年02月17日 14:07
  • 730

Mybatis+Oracle进行数据的批量插入和更新

Mybatis是web工程开发中非常常用的数据持久化的框架,通过该框架,我们非常容易的进行数据库的增删改查。数据库连接进行事务提交的时候,需要耗费的资源比较多,如果需要插入更新的数据比较多,而且每次事...
  • w_y_t_
  • w_y_t_
  • 2016年05月15日 11:04
  • 6753
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE批量更新方法
举报原因:
原因补充:

(最多只允许输入30个字)