MERGE INTO 简介

我们用注释的方式来介绍更形象一些

MERGE INTO bonuses d

USING (SELECT employee_id, salary,department_id

        FROM oe.employees

       WHERE department_id = 80) s ON (d.employee_id = s.employee_id) /*匹配条件d.employee_id =s.employee_id */

WHEN MATCHED THEN/*当d表中存在与S对应数据时进行更新或删除*/

 UPDATE

    SET d.bonus = d.bonus + s.salary * .01/*WHERE只能出现一次，如果在这儿加了WHERE，那后面DELETE后面的WHERE就无效*/

 DELETE

  WHERE (s.salary > 8000) /*删除时，只更新s.salary>8000时的数据*/

WHEN NOT MATCHED THEN/*当d表中不存在在与S对应数据时进行新增*/

 INSERT

   (d.employee_id, d.bonus)

 VALUES

   (s.employee_id, s.salary * .01)

  WHERE (s.salary <= 8000) /*新增时，只更新s.salary <= 8000时的数据，注意这儿与前面不一样，是D表中不存在对应数据时才新增*/;

/*以上语句相当于在同时执行以下三条*/

DELETE bonuses d

 WHERE EXISTS (SELECT null

          FROM oe.employees s

         WHERE s.department_id = 80

           AND s.salary > 8000

           AND d.employee_id = s.employee_id);

 

UPDATE bonuses d

   SET d.bonus =

       (SELECT d.bonus + s.salary * .01

          FROM oe.employees s

         WHERE s.department_id = 80

           AND d.employee_id = s.employee_id)

 WHERE EXISTS (SELECT d.bonus + s.salary * .01

          FROM oe.employees s

         WHERE s.department_id = 80

           AND s.salary <= 8000

           AND d.employee_id = s.employee_id) ;

 

INSERT INTO bonuses d

  (d.employee_id, d.bonus)

  SELECT s.employee_id, s.salary * .01

    FROM oe.employees s

   WHERE s.department_id = 80

     AND s.salary <=8000

     AND NOT EXISTS

   (SELECT NULL FROM bonuses d WHERE d.employee_id= s.employee_id);