[ORACLE]MERGE INTO 구문

MERGE INTO 구문

MERGE INTO TABLE_NAME A

USING (

            SELECT .....

                FROM ....

                WHERE ....

             => 다른 테이블에서 가공된 자료

        ) B

    ON (

            A.COLUMN_NAME = B.COLUMN_NAME

            AND

               A...... = B......

        => 업데이트가 되게하기 위한 조건   

        )

WHEN MATCHED THEN UPDATE ....  => 조건이 맞으면 UPDATE 

                                SET ....

WHEN NOT MATCHED THEN INSERT ....        => 9i에서는 인서트까지 꼭 써줘야 한다.. 10g부터는 인서트는 없어도 된다..

                                               VALUES ( ....)

  Examples - In the Xperts knowledge

Merging into a Table: Example The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
 (SELECT e.employee_id FROM employees e, orders o
 WHERE e.employee_id = o.sales_rep_id
 GROUP BY e.employee_id);

SELECT * FROM bonuses;

EMPLOYEE_ID BONUS
----------- ----------
 153 100
 154 100
 155 100
 156 100
 158 100
 159 100
 160 100
 161 100
 163 100

MERGE INTO bonuses D
 USING (SELECT employee_id, salary, department_id FROM employees
 WHERE department_id = 80) S
 ON (D.employee_id = S.employee_id)
 WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
 DELETE WHERE (S.salary > 8000)
 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
 VALUES (S.employee_id, S.salary*0.1)
 WHERE (S.salary <= 8000);

EMPLOYEE_ID BONUS
----------- ----------
 153 180
 154 175
 155 170
 159 180
 160 175
 161 170
 179 620
 173 610
 165 680
 166 640
 164 720
 172 730
 167 620
 171 740