MERGE INTO用法

作用：一条SQL语句从一个或多个数据源中完成对表的更新或插入数据，例如现在两张表，两张表都有bsm，通过merge into可以把bsm一样的一张表里的数据复制到另外一张表中。可同时进行INSERT和UPDATE操作，为DML语句，需要COMMIT或ROLLBACK提交回滚事务。

用法：9i引入，使用此命令时需要同时指定UPDATE和INSERT关键词

      10g改动

      1、UPDATE和INSERT子句都是可选用的

      2、UPDATE和INSERT子句可以加WHERE子句

      3、在ON条件中可以使用常量过滤谓词来INSERT所有行到目标表，不需要连接主表和目标表

      4、UPDATE子句后面可以跟DELETE子句删除一些不需要的行

语法：

      MERGE INTO [your table-name] [rename your table here] 

           USING ( [write your query here] )[rename your query-sql and using just like a table] 
           ON ([conditional expression here] AND [...]...) 
           WHEN MATHED THEN [here you can execute some update sql or something else ] 
           WHEN NOT MATHED THEN [execute something else here ] 

实例：

 CREATE TABLE sourcetab(   

    ID INTEGER,   

    NAME VARCHAR2(60),   

    INFO VARCHAR2(60)   

 );   

  

INSERT INTO sourcetab VALUES(1501, 'VIVITAR 35MM', 'ELECTRNCS');   

INSERT INTO sourcetab VALUES(1502, 'OLYMPUS IS50', 'ELECTRNCS');   

INSERT INTO sourcetab VALUES(1600, 'PLAY GYM', 'TOYS');   

INSERT INTO sourcetab VALUES(1601, 'LAMAZE', 'TOYS');   

INSERT INTO sourcetab VALUES(1666, 'HARRY POTTER', 'DVD');   

COMMIT;   

  

 CREATE TABLE desttab(   

    ID INTEGER,   

    NAME VARCHAR2(60),   

    USEAGE VARCHAR2(60)   

 );   

  

INSERT INTO desttab VALUES(1502, 'OLYMPUS CAMERA', 'ELECTRNCS');   

INSERT INTO desttab VALUES(1601, 'LAMAZE', 'TOYS');   

INSERT INTO desttab VALUES(1666, 'HARRY POTTER', 'TOYS');   

INSERT INTO desttab VALUES(1700, 'WAIT INTERFACE', 'BOOKS');   

COMMIT;   

MERGE INTO desttab d USING sourcetab s|(SELECT * FROM sourcetab) s  --USING后可使用子查询或视图

ON (d.ID=s.ID)      --匹配条件

WHEN MATCHED THEN 

UPDATE SET d.name=s.name,d.info=s.info

WHEN NOT MATCHED THEN

INSERT (d.id,d.name,d.info)  VALUES(s.id,s.name,s.info);

下面查看一下10g新特性

1、UPDATE和INSERT子句都是可选用的

比较容易理解，可省略matched或not matched子句

2、UPDATE和INSERT子句可以加WHERE子句

MERGE INTO desttab d USING sourcetab s

ON (d.ID=s.ID)      

WHEN MATCHED THEN 

UPDATE SET d.name=s.name,d.info=s.info

WHERE s.info= 'TOYS'   --匹配后只更新源表名字为TOYS的记录

WHEN NOT MATCHED THEN

INSERT (d.id,d.name,d.info)  VALUES(s.id,s.name,s.info)

WHERE s.info='TOYS';   --不匹配后只更新源表名字为TOYS的记录

3、在ON条件中可以使用常量过滤谓词来INSERT所有行到目标表，不需要连接主表和目标表

MERGE INTO desttab d USING sourcetab s

ON (1=0)  --肯定不匹配，只插入 直接INSERT INTO会更简单

WHEN MATCHED THEN 

UPDATE SET d.name=s.name,d.info=s.info

WHEN NOT MATCHED THEN

INSERT (d.id,d.name,d.info)  VALUES(s.id,s.name,s.info);

4、UPDATE子句后面可以跟DELETE子句删除一些不需要的行

MERGE INTO desttab d USING sourcetab s

ON (d.ID=s.ID)      

WHEN MATCHED THEN 

UPDATE SET d.name=s.name,d.info=s.info

DELETE WHERE s.info= 'TOYS'   --匹配后只删除目标表信息为TOYS的记录DELETE d WHERE d.id=s.id AND s.info='TOYS'

WHEN NOT MATCHED THEN

INSERT (d.id,d.name,d.info) VALUES(s.id,s.name,s.info);