DB2中merge相关用法，请举例说明

Question

如题所述

Answer 1

在数据仓库中的转换和装载过程中，经常会使用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_TYPE3 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.ID4 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 T2 MINUS3 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.ID4 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.ID5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);5775 行已合并。本文来自CSDN博客，转载请标明出处： http://blog.csdn.net/levma/archive/2007/06/18/1656396.aspx

Answer 2

再寻求帮助