oracle merge into的使用

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

在做报表的时候，遇到这么一个问题，由于数据量大，数据上传时间问题，经常要处理更新几天前的数据，这时需要涉及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排序方式对比一下结果。

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