MERGE 머지

INSERT 나 UPDATE 에서 조건을 추가할 수 있고, 여러 행 삭제 DELETE도 한번에 수행 가능하게 끔 한다.

 

ON 조건에 일치하면, UPDATE 하고, 불일치하면 INSERT 하는 쿼리문

 

// 머지를 이용하여, 사번 100번이 있으면 부서를 20으로 변경하고,

// 없으면 101번 (first_name) Neena 사용자를 20번 부서로 넣으시오.

 

MERGE INTO [table/view] // update, insert 할 대상
USING [table/view/dual] // 위 table/view와 동일하면 dual 사용
ON [조건] 			  // update, insert 할 조건문(조건 일치 update/ 불일치 insert)
WHEN MATCHED THEN
UPDATE SET
[column1] = [value1],
[column2] = [value2],
...
(DELETE [table] WHERE [column1]=[VALUE1] AND...) // update 뿐 아니라, delete 도 가능
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...)
VALUES (VALUE1, VALUE2, ...)

MERGE INTO employees USING dual ON (employee_id = '100')
 WHEN MATCHED THEN
 UPDATE
  SET department_id = '20'
 WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, department_id)
 VALUES(101,'Neena',20);
 // 비교 대상이 merge into table_name 의 table_name이 동일하면 dual 사용
 
 MERGE INTO employees USING dual ON (employee_id = '99')
 WHEN MATCHED THEN
 UPDATE
  SET department_id = '20'
 WHEN NOT MATCHED THEN
 UPDATE 
  SET commission_pct = '0.3'
where employee_id = '101';
// ERROR
// merge 구문은 앞에 UPDATE쓰고, 뒤에는 INSERT 를 쓰는 문법으로만 가능하다.

SQL> select * from employees1
  2  where employee_id = '100';

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY
------------------------- -------------------- -------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        100 Steven               King
SKING                     515.123.4567         87/06/17 AD_PRES         29040
                                     20
// employee_id 100 번이 있어서, 부서가 20번으로 옮겨졌고,
// 앞에 구문이 update 되었기에, 뒤에 insert 구문은 사용되지 않았다.

SQL> select department_id, first_name from employees
  2  where first_name = 'Neena'
  3  ;

DEPARTMENT_ID FIRST_NAME
------------- --------------------
           90 Neena

// 판매 부서의 사원들에게 급여의 10%와 $100을 보너스로 주고,
// 그 외의 부서에 대해서는 급여의 10%만 보너스를 주되,
// 급여가 $8000 이하의 직원에 대해서만 적용 시키시오.

1. bonus table 생성
create table bonus(
employee_id number,
bonus number
default 100
);
// bonus의 record는 없음. bonus column에 별도의 default(기본값) 값을 준다.

2. bonus table에 sales 인원 넣기
insert into bonus(employee_id)
select employee_id 
from employees e JOIN departments d ON d.department_id = e.department_id
where department_name = 'Sales';
34개의 행이 만들어졌습니다.
// sales 부서의 인원은 34.

MERGE INTO bonus b USING (select employee_id,salary,department_id from employees) e
ON (e.employee_id = b.employee_id) // ON 뒤에 괄호를 해야 오류가 안남.
WHEN MATCHED THEN
UPDATE
SET bonus = salary * 1.1
DELETE WHERE salary > '8000'
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, salary*0.1)
WHERE salary <= '8000';
// USING (select employee_id,salary,department_id from employees) e ON (e.employee_id = b.employee_id)
// when matched 와 when not matched가 위의 조건에 성립이 되기에 둘다 실행이 된다.
// Neena 문제에서는 employee_id=100이라는 조건을 주었기에 그 조건이 실행 된 후, insert문이 실행되지 않았다.

select b.employee_id, bonus, salary, department_id
from bonuses b JOIN employees e
ON e.employee_id = b.employee_id
// 확인