(Oracle DB) - 18. MERGE

/* MERGE 
	#	Condition 비교
		- Data의 condition result == 'false' in the Table
			* INSERT the data
		- Data의 condition result == 'true' in the table
			* UPDATE the data
*/


/* Format of MERGE */
MERGE INTO table_name
	USING (subquery / table)	-- Source data for UPDATE / INSERT
		ON	(condition)	-- Condition to join the source & destination tables : Define how the rows should be matched between source & destination tables
WHEN MATCHED THEN
	UPDATE
		SET(
			column1 = value,
			column2 = value,
			...)
	WHERE / DELETE WHERE	-- WHERE works in case of UPDATE / DELETE WHERE works in case of DELETE
		(condition)			-- Condition for UPDATE
WHEN NOT MATCHED THEN
	INSERT					-- INTO has been written next to MERGE
		(column1, column2, ...)
		VALUES
		(value, value, ...)
	WHERE
		(condition)			-- Condition for INSERT
;


---------------------------------------------------- Practice of MERGE with Two Tables -------------------------
/*
	#	Step 1. 먼저 merge용도의 table을 CREATE
	#	Step 2. 조건에 맞는 data가 있으면 UPDATE하고, 없다면 INSERT
*/

CREATE TABLE ex3_3 (
	employee_id    NUMBER,
	additional_bonus NUMBER DEFAULT 0
);

	/* Milestone of MERGE
	
		#	sales table에서, 2000년 10월 ~ 12월까지 매출 달성한 사원의 employee_id를 조회
			-	해당 employee_id를 INSERT INTO ex3_3
				*	GROUP BY로 duplicated data를 방지
				
		#	employees table에서, manager_id가 146번인 사원(x)을 조회
			-	x의 employee_id가, ex3_3 table에 insert된 사원과 일치한다면,
				additional_bonus에 salary의 5%를 bonus로 UPDATE
			-	일치하지 않는다면, x를 ex3_3 table에 INSERT해주고,
				x의 salary가 8000 미만인 경우 additional_bonus (5% of salary)
	*/


	/*
		sales table에서, 2000년 10월 ~ 12월까지 매출 달성한 사원의 employee_id를 조회,
		manager_id를 활용할 것이므로 employees table과 join
	*/
SELECT s.employee_id
	FROM sales s, employees e
WHERE 1 = 1
AND s.employee_id = e.employee_id
AND s.sales_month
	BETWEEN '200010' AND '200012'

;	-- 55,984


	/*
		해당 employee_id를 INSERT INTO ex3_3 
		GROUP BY로 duplicated data를 방지
	*/
INSERT INTO ex3_3
	(employee_id)
	(
	SELECT s.employee_id
		FROM sales s, employees e
	WHERE 1 = 1
	AND s.employee_id = e.employee_id
	AND s.sales_month
		BETWEEN '200010' AND '200012'
	GROUP BY s.employee_id
	)
;	-- 5 rows inserted.

	/* INSERT된 employee_id를 조회 */
SELECT *
	FROM ex3_3
ORDER BY employee_id
;


	/*
		#	UPDATE 대상을 조회
		employees table에서, manager_id가 146번이면서
		employee_id가 ex3_3 table에 insert된 사원과 일치하는 사원(x)을 조회
		(additional_bonus가 salary의 5%)
	*/
SELECT
	employee_id,
	manager_id,
	salary,
	salary * 0.05
		AS additional_bonus
	FROM employees
WHERE 1 = 1
AND manager_id = 146
AND employee_id
	IN (SELECT employee_id FROM ex3_3)
;	-- 1 명


	/*
		#	INSERT 대상을 조회
		employees table에서, manager_id가 146번이면서
		employee_id가 ex3_3 table에 insert된 사원과 일치하지 않는 사원(Y)을 조회
		(Y의 salary가 8000 미만인 경우, additional_bonus가 salary의 2%)
	*/
SELECT
	employee_id,
	manager_id,
	salary,
	salary * 0.02
		AS additional_bonus
	FROM employees
WHERE 1 = 1
AND manager_id = 146
AND salary < 8000
AND employee_id
	NOT IN (SELECT employee_id FROM ex3_3)
;


	-- MERGE
MERGE INTO ex3_3 ex
	USING
		(
		SELECT
			emp.employee_id, emp.manager_id,
			emp.salary
			FROM employees emp
		WHERE emp.manager_id = 146
		) proc
	ON (ex.employee_id = proc.employee_id)
WHEN MATCHED THEN
	UPDATE
		SET ex.additional_bonus = proc.salary * 0.05	
WHEN NOT MATCHED THEN
	INSERT
		VALUES
		(proc.employee_id, proc.salary * 0.01)
	WHERE 1 = 1
	AND proc.salary < 8000
;


/* MERGE - DELETE WHERE */
MERGE INTO ex3_3 ex
	USING
		(
		SELECT
			emp.employee_id, emp.manager_id,
			emp.salary
			FROM employees emp
		WHERE emp.manager_id = 146
		) proc
	ON (ex.employee_id = proc.employee_id)
WHEN MATCHED THEN
	UPDATE
		SET ex.additional_bonus = proc.salary * 0.05
	DELETE WHERE
		ex.employee_id = 161
WHEN NOT MATCHED THEN
	INSERT
		VALUES
		(proc.employee_id, proc.salary * 0.01)
	WHERE 1 = 1
	AND proc.salary < 8000
;


SELECT *
	FROM ex3_3
ORDER BY employee_id
;