Total Pageviews

May 18, 2015

5/18/2015 12:27:00 PM

Oracle Merge Statement



Oracle Merge Statement

MERGE Statement


Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i.


It is also known as UPSERT i.e. combination of UPDATE and INSERT.


The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an "upsert". The MERGE statement reduces table scans and can perform the operation in parallel if required.


MERGE Statement Enhancements in Oracle Database 10g


Syntax

 Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.


MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);



 The source can also be a query.



MERGE INTO employees e

    USING (SELECT * FROM hr_records WHERE job='MANAGER') h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address)



One More example:



MERGE INTO   emp

     USING   new_emp src

        ON   (emp.empno = src.empno)

WHEN MATCHED

THEN

   UPDATE SET sal = src.sal, job = src.job, deptno = src.deptno

   DELETE

           WHERE   job = 'Resigned'

WHEN NOT MATCHED

THEN

   INSERT              (empno,

                        sal,

                        ename,

                        job,

                        deptno)

       VALUES   (src.empno,

                 src.sal,

                 src.ename,

                 src.job,

                 src.deptno)



new records in NEW_EMP  represent newly hired employees; these should be created in the EMP table records in NEW_EMP with a corresponding (matched by EMPNO) record in EMP represented changed Employees records – new job, salary or deptno; the EMP records should be updated from their matches in NEW_EMP

 finally, we have a special requirement – well, actually two:

when an Employees was  resigned, he will be in the NEW_EMP table with his or her job set to Resigned ; the corresponding record in the EMP table should be removed


We also have records in the EMP table for temporary employees (TEMPs); their Job is set to TEMP; the temporary records do not get fed from the external systems, they will never appear in NEW_EMP. Whenever we refresh the EMP table from NEW_EMP, we want all TEMPs to be removed from the EMP table



Watch out for these when you MERGE


You cannot update any of the columns you are merging on. If you try updating a student’s id in the example above, this error will show up in 10G:


ORA-38104: Columns referenced in the ON Clause cannot be updated


MERGE is a deterministic statement – that is, you cannot update a row of the target table multiple times in the same MERGE statement.


You must have the INSERT and UPDATE privileges on the target table and the SELECT privilege on the source table. To specify the DELETE clause, you must also have the DELETE privilege on the target table.

When using MERGE for the DELETE operation, remember that:

 DELETE  checks the match condition on the target table, not the source.

 DELETE works only on rows updated during MERGE. Any rows in the target table that are not processed during MERGE are not deleted, even if they match the DELETE condition.


 
Related Posts Plugin for WordPress, Blogger...