SQL Merge Statement

Merge Statement

Merge Statement:- Merge statement is used for insert and update and delete operation simultaneously in Oracle 10g onward.

Limitation in Oracle 9i :-Merge statement introduce in Oracle 9i and there were below  limitations

1) We can not execute only update statement

2) We can not execute only insert statement

3) There was no delete statement

4) Source can be select statement but with only one table means we can not use joins and Set Operators query as a source.

Note:- All above limitation have been removed in Oracle 10g.

Limitation in Oracle 10g :- We can not use only delete statement. Delete statement is used with update statement.

Setup for LAB Test :-

SQL> --Matching records
SQL> SELECT count(*) FROM emp1 a,emp2 b WHERE a.empno=b.empno;

  COUNT(*)
----------
         8

SQL> --Not Matching records
SQL> select count(*) from (select * from emp2 minus select * from emp1);

  COUNT(*)
----------
         6
Example1:-
 

SQL> MERGE INTO emp1 a --Target Table
  2      USING EMP2 b  --Source Table
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN
  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);

14 rows merged.

Example2:-

 
SQL> MERGE INTO emp1 a                 --Target Table
  2      USING (SELECT * FROM emp2) b  --Source Inline View
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN
  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);

14 rows merged.
 

Note:-"AS" clause is not allowed in Merge Statement

SQL>MERGE INTO emp1 AS a --Target Table
  2      USING EMP2 AS b  --Source Table
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);
MERGE INTO emp1 AS a --Target Table
                *
ERROR at line 1:
ORA-02012: missing USING keyword       

Error Logging with Merge:-

 
create table copy_emp as select * from employees;

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('copy_emp', 'TAB_ERR_COPY_EMP');

select * from TAB_ERR_COPY_EMP;


MERGE INTO copy_emp c
     USING employees e
     ON (c.employee_id = e.employee_id)
   WHEN MATCHED THEN
     UPDATE SET
       c.first_name     = e.first_name,
       c.last_name      = e.last_name,
       c.email          = e.email,
       c.phone_number   = e.phone_number,
       c.hire_date      = e.hire_date,
       c.job_id         = e.job_id,
       c.salary         = e.salary,
       c.commission_pct = e.commission_pct,
       c.manager_id     = e.manager_id,
       c.department_id  = e.department_id
   WHEN NOT MATCHED THEN
     INSERT VALUES(e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number, e.hire_date, e.job_id,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id)
LOG ERRORS INTO TAB_ERR_COPY_EMP('TAG_STATEMENT') REJECT LIMIT 1000;
       
       
Note:-There are some limitations for the error_logging_clause.

    1) The following conditions cause the statement to fail and roll back without invoking the error logging capability
        a) Violated deferred constraints.
        b) Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
        c) Any update operation UPDATE or MERGE that raises a unique constraint or index violation.

    2) You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.
        a) If you create or modify the corresponding error logging table so that it contains a column of an unsupported type, and if the name of that column corresponds to an unsupported column in the target DML table, then the DML statement fails at parse time.
        b) If the error logging table does not contain any unsupported column types, then all DML errors are logged until the reject limit of errors is reached. For rows on which errors occur, column values with corresponding columns in the error logging table are logged along with the control information.