`

MERGE语法

SQLOracleFPXPWindows 
阅读更多

24/05/2005 14:37 FP 在数据仓库中的转换和装载过程中，经常会使用MERGE语句，这里简单总结一下。


MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。
下面看个具体的例子：
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS
2 SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
3 FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
6165 行已合并。
SQL> SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
2 MINUS
3 SELECT * FROM T1;
未选定行
MERGE语法其实很简单，下面稍微修改一下例子。
SQL> DROP TABLE T;
表已丢弃。
SQL> DROP TABLE T1;
表已丢弃。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
MERGE INTO T1 USING T
*
ERROR 位于第 1 行:
ORA-30926: 无法在源表中获得一组稳定的行
这个错误是使用MERGE最常见的错误，造成这个错误的原因是由于通过连接条件得到的T的记录不唯一。最简单的解决方法类似：
SQL> MERGE INTO T1
2 USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
5775 行已合并。
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错，详细信息可以参考：http://blog.itpub.net/post/468/14844

===============================================================

ref: http://tomszrp.itpub.net/post/11835/263865

在Oracle 10g之前，merge语句支持匹配更新和不匹配插入2种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

　

参考Oracle 的SQL Reference，大家可以看到Merge Statement的语法如下：
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

下面我在windows xp 下10.2.0.1版本上做一个测试看看

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
一、创建测试用的表
SQL> create table subs(msid number(9),
2                    ms_type char(1),
3                    areacode number(3)
4                    );
表已创建。
SQL> create table acct(msid number(9),
2                    bill_month number(6),
3                    areacode   number(3),
4                    fee        number(8,2) default 0.00);
表已创建。
SQL>
SQL> insert into subs values(905310001,0,531);
已创建 1 行。
SQL> insert into subs values(905320001,1,532);
已创建 1 行。
SQL> insert into subs values(905330001,2,533);
已创建 1 行。
SQL> commit;
提交完成。
SQL>
　
二、下面先演示一下merge的基本功能
1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);
3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5     when NOT MATCHED then
6          insert(msid,bill_month,areacode)
7          values(b.msid,'200702',b.areacode);
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
SQL> insert into subs values(905340001,3,534);
1 row inserted
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3            534
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when NOT MATCHED then
4          insert(msid,bill_month,areacode)
5          values(b.msid,'200702',b.areacode);
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
905340001     200702      534       0.00
SQL> update subs set areacode=999;
4 rows updated
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3            999
905310001 0            999
905320001 1            999
905330001 2            999
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
905340001     200702      534       0.00
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      999       0.00
905330001     200702      999       0.00
905310001     200702      999       0.00
905340001     200702      999       0.00
SQL>
　
三、10g中增强一：条件操作
1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          where b.ms_type=0
6     when NOT MATCHED then
7          insert(msid,bill_month,areacode)
8          values(b.msid,'200702',b.areacode)
9          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
SQL> insert into subs values(905360001,0,536);
1 row inserted
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0            536
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when NOT MATCHED then
4          insert(msid,bill_month,areacode)
5          values(b.msid,'200702',b.areacode)
6          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
905360001     200702      536       0.00
SQL> update subs set areacode=888 where ms_type=0;
2 rows updated
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0            888
905310001 0            888
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
905360001     200702      536       0.00
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      888       0.00
905360001     200702      888       0.00
SQL>
四、10g中增强二：删除操作
An optional DELETE WHERE clause can be used to clean up after a
merge operation. Only those rows which match both the ON clause
and the DELETE WHERE clause are deleted.
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL>  merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          delete where (b.ms_type!=0);
Done
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
SQL>
更为详尽的语法，请参考Oracle SQL Reference手册！
分享到：
| awk文本处理总结（入门，中级，高级）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Mysql和Oracle的50个区别PDF

    1.有些人说 Oracle没有限制,实际上他们说得可能是Oracle不支持LIMIT 语法.不过可以rownum 虚列来实现,...7. INSERT … ON DUPLICATE KEY UPDATE 语法将不再可用,不过你需要学习更加复杂(但是符合SQL标准)的MERGE 语法,

    Oracle MERGE INTO的用法示例介绍

    如果不使用Oracle提供的merge语法的话,可能先要上数据库select查询一下看是否存在,然后决定怎么操作,这样的话需要写更多的代码,同时性能也不好,要来回数据库两次.使用merge的话则可以一条SQL语句完成. 1）主要功能 ...

    Killtest 免费提供 1Z0-030 资料下载

    正确答案解释了MERGE 语法的功能：对于表T2中的每一行，如果它已经存在于表T1中，则更新该行；如果不存在，则将其插入到表T1中。这是MERGE 语句的一个常见用法，可以简化数据同步任务。 ### 3. PGA_AGGREGATE_...

    SQL Server Merger实现数据的同步

    #### Merge语法介绍 在SQL Server 2008及更高版本中引入了`MERGE`语句，这一功能允许用户在一个语句中根据源表与目标表之间的联接结果来执行插入、更新或删除操作。具体来说，可以根据源表与目标表的匹配情况有条件...

    AraxisMerge绿色版

    它能识别各种代码语法，高亮显示，使得代码比较更加清晰。同时，它还可以直接编辑和合并文件，不仅查看差异，还能直接解决差异，真正实现了一站式操作。 总的来说，Araxis Merge是一款强大的文件对比工具，它的绿色...

    WinMerge文件比较工具

    - **自定义语法高亮**：支持对代码文件进行语法高亮，提高阅读和理解代码的效率。 - **行和列选择**：允许用户选择特定行或列进行比较，便于聚焦于关键部分。 - **上下文敏感**：WinMerge会根据文件类型自动调整...

    Mybatis批量foreach merge into的用法

    Mybatis批量foreach merge into的用法是通过Mybatis的动态SQL语法foreach循环插入实现的，这种方法可以批量插入时间价格表数据。如果某个套餐的某天的价格存在，则更新，不存在则插入。下面是该方法的详细介绍： ...

    oracle Merge 函数.doc

    Merge 函数的基本语法结构为： ```sql MERGE INTO 目标表 USING 来源表 ON (条件) WHEN MATCHED THEN 更新操作 WHEN NOT MATCHED THEN 插入操作 ``` 其中，目标表是要更新或插入的表，来源表是提供更新或插入数据的...

    WinMerge.rar

    - **语法高亮**：对于代码文件，WinMerge支持语法高亮，使代码对比更加直观。 - **过滤规则**：用户可以根据需要设置过滤规则，忽略某些特定类型的文件或文件夹，提高比较效率。 2. **界面和操作：** - **用户...

    sql server 2008

    - **MERGE 语法**：MERGE 语句是一种强大的数据同步工具，可以在单条语句中执行 INSERT、UPDATE 和 DELETE 操作。其基本结构如下： ```sql MERGE TargetTable USING (SELECT * FROM SourceTable) AS Source ON ...

    代码比对工具-WinMerge

    - **语法高亮**：在对比代码时，WinMerge支持多种编程语言的语法高亮，使得代码更易于阅读。 - **可自定义设置**：用户可以根据自己的需求调整比对规则，例如忽略空格、换行符或者特定字符。 **2. 使用场景** - *...

    WinMerge(绿色)

    社区中存在许多第三方插件，如增强的语法高亮、支持更多文件类型等，进一步增强了WinMerge的实用性。 6. **版本兼容性**：WinMerge适用于Windows操作系统，能够良好地运行在不同的Windows版本上，包括最新的Windows...

    Araxis Merge 2001 Professional Evaluation

    5. **源代码友好**：对于代码文件，Merge能够理解编程语法，只显示有意义的差异，避免因格式差异导致的误判。 6. **跨平台支持**：虽然描述中没有明确提到，但通常专业级别的合并工具会支持多种操作系统，比如...

    WinMerge文件对比软件

    此外，它还支持多种编程语言的语法高亮，使得代码对比更加清晰易读。 在实际使用中，WinMerge与版本控制系统（如Git、SVN）配合使用，可以作为图形化的差异和合并工具。例如，当你在Git仓库中解决合并冲突时，可以...

    WinMerge-2.12.4-Setup

    5. **语法高亮**：对于程序员来说，WinMerge支持多种编程语言的语法高亮，使得代码对比更加直观。 6. **自定义设置**：用户可以根据自己的需求调整比较的规则，比如忽略空格、注释或者特定的字符，也可以设置对比的...

    android merge和include简单使用

    `include`标签的基本语法如下： ```xml ``` 这里的`layout_name`是你要引入的布局文件名，它应该位于`res/layout`目录下。 在实际使用`merge`和`include`时，有几点需要注意： 1. 当`include`标签用于`merge`...

    Merge into的使用详解

    一、`MERGE INTO`的基本语法 `MERGE INTO`语句的基本结构如下： ```sql MERGE INTO target_table USING source_table ON (join_condition) WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, .....

    非常好用的文本代码比对工具 WinMerge

    3. 面向开发者的设计：对于程序员来说，WinMerge支持语法高亮显示，使得代码比对更加直观。同时，它可以与常见的版本控制系统（如Git、SVN等）无缝集成，方便开发者处理代码冲突。 4. 多语言支持：WinMerge界面支持...

Magicbox
Global site tag (gtag.js) - Google Analytics