我们用注释的方式来介绍更形象一些
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表中不存在对应数据时才新增*/;
/*以上语句相当于在同时执行以下三条*/
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);