Oracle MERGE statement
MERGE statement, which can update or insert a table or view at the same time in a SQL statement, avoiding multiple INSERT, UPDATE and DELETE statements. It can select data from one or more data sources to update or insert the target table, and you can specify the conditions under which to update or insert the target table or view. You cannot update the same row multiple times. For example: implement to update the data of A table with B table, and insert the data of B table into A table if it is not in A table.
The 11gR2 syntax is as follows:
MERGE [hint] INTO [schema. ] {table | view} [t_alias] USING {[schema. ] {table | view} | subquery} [t_alias] ON (condition) [merge_update_clause] [merge_insert_clause] [error_logging_clause] # merge_update_clause WHEN MATCHED THENUPDATE SET column = {expr | DEFAULT} [, column = {expr | DEFAULT}]... [where_clause] [DELETE where_clause] # merge_insert_clauseWHEN NOT MATCHED THENINSERT [(column [, column]...)] VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]. ) [where_clause] # error_logging_clauseLOG ERRORS [INTO [schema.] Table] [(simple_expression)] [REJECT LIMIT {integer | UNLIMITED}]
Use an example to illustrate the purpose of the MERGE statement:
Create a test table emp_m
Scott@TEST > create table emp_m as select * from emp where comm is not null;Table created.scott@TEST > select * from emp_m EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 1981 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
Example 1: insert and update operations
Now that there is only data in the test table emp_m that comm is not empty, now insert the data in the emp table instead of the emp_ m table into the emp_ m table, and increase the comm of the existing data in the emp_ m table by 10.
Scott@TEST > MERGE INTO EMP_M M 2 USING EMPN 3 ON (M.EMPNO = N.EMPNO) 4 WHEN NOT MATCHED THEN 5 INSERT 6 (M.EMPNO, M.ENAME, M.JOB, M.MGR, M.HIREDATE, M.SAL, M.COMM, M.DEPTNO) 7 VALUES 8 (N.EMPNO, N.ENAME, N.JOB, N.MGR, N.HIREDATE, N.SAL, N.COMM, N.DEPTNO) 9 WHEN MATCHED THEN 10 UPDATE SET M.COMM = M.COMM + 10 14 rows merged.scott@TEST > select * from emp_m EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 310 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 510 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1410 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 10 30 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06- 09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.
Example 2: implement delete operation
Delete the record of DEPTNO=30 in table emp from the emp_ m table.
Scott@TEST > MERGE INTO EMP_M M 2 USING (SELECT EMPNO FROM EMP WHERE DEPTNO = 30) N 3 ON (M.EMPNO = N.EMPNO) 4 WHEN MATCHED THEN 5 UPDATE SET M.COMM = DEFAULT 6 DELETE WHERE M.EMPNO = N.EMPNOTIX 6 rows merged.scott@TEST > select * from emp_m EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Considerations for use:
You cannot specify a view on which an INSTEAD OF trigger is defined as the target view.
The USING subname specifies the source data, which can be a table, view, or subquery
The where condition in the UPDATE clause can specify the condition of the target table or the condition of the source table.
Columns in the ON condition cannot be updated in the UPDATE clause, and DEFAULT cannot be specified when updating the table
The DELETE WHERE is specified in the UPDATE clause to delete data.
In the INSERT clause, if the column name is omitted after INSERT, the number of columns after VALUES must be the same as the number of columns in the target table
In the INSERT clause, you can use WHERE conditions to restrict the inserted data
Official document: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
