[SQL BOOSTER] 4.2 MERGE

📚 SQL BOOSTER (유일환, DBian)

📌오라클 → PostgreSql 15로 변환

 

CHAP4. 유용한 SQL 문법

 

4.2.1 MERGE

MERGE는 한 문장으로 INSERT와 UPDATE를 동시에 처리할 수 있다.

한 건의 데이터는 INSERT와 UPDATE 중 하나만이 수행된다.

MERGE 대상이 이미 존재하면 UPDATE를, 대상이 존재하지 않으면 INSERT를 수행하는 방식이다.

-- ************************************************
-- PART I - 4.2.1 SQL1
-- ************************************************

-- MERGE 문을 위한 테스트 테이블 생성
-- 'CREATE TABLE  AS'(줄여서 CTAS)
-- CTAS로는 테이블의 PK까지는 생성되지 않음
create table M_CUS_CUD_TEST as
select	*
from	M_CUS T1;

alter table M_CUS_CUD_TEST
	add constraint PK_M_CUS_CUD_TEST primary key (CUS_ID);


-- ************************************************
-- PART I - 4.2.1 SQL2
-- ************************************************

-- CUS_0090 고객을 입력하거나 변경하는 PL/SQL
do $$
declare
	v_EXISTS_YN VARCHAR(1);
begin
	-- 고객ID가 이미 있는지 확인하는 SQL
	select COALESCE(MAX('Y'), 'N')
	into v_EXISTS_YN
	from M_CUS_CUD_TEST T1
	where T1.CUS_ID = 'CUS_0090';

	-- Conditional Logic for inserting or updating
	if v_EXISTS_YN = 'N' then
		-- Insert new customer
		insert into M_CUS_CUD_TEST (CUS_ID, CUS_NM, CUS_GD)
		values ('CUS_0090', 'NAME_0090', 'A');
	
		raise notice 'INSERT NEW CUST';
	else
		-- Update existing customer
		update M_CUS_CUD_TEST T1
		set T1.CUS_NM = 'NAME_0090',
			T1.CUS_GD = 'A'
		where T1.CUS_ID = 'CUS_0090';
		
		raise notice 'UPDATE OLD CUST';
	end if;
		
	commit;
end $$;


-- ************************************************
-- PART I - 4.2.1 SQL3
-- ************************************************

-- 고객을 입력하거나 변경하는 SQL – MERGE 문으로 처리
-- INSERT와 UPDATE를 나누어 개발하는 것이 명확한 경우가 더 많음
merge into M_CUS_CUD_TEST T1 -- MERGE 대상
using (
	select 	'CUS_0090' as CUS_ID,
			'NAME_0090' as CUS_NM,
			'A' as CUS_GD
) T2 -- 비교대상 (실제 테이블도 사용 가능)
on (T1.CUS_ID = T2.CUS_ID) -- 비교 조건
when matched then
	update set 	CUS_NM = T2.CUS_NM,
				CUS_GD = T2.CUS_GD
when not matched then
	insert (CUS_ID, CUS_NM, CUS_GD)
	values (T2.CUS_ID, T2.CUS_NM, T2.CUS_GD);

 

4.2.2 MERGE를 사용한 UPDATE

MERGE 문장에서 WHEN MATCHED THEN 절만 사용하면, 해당 MERGE 문은 UPDATE만 처리한다.

-- ************************************************
-- PART I - 4.2.2 SQL1
-- ************************************************

-- 월별고객주문 테이블 생성 및 기조 데이터 입력
-- 테이블 생성
create table S_CUS_YM
(
	BAS_YM	VARCHAR(6) not null,
	CUS_ID	VARCHAR(40) not null,
	ITM_TP	VARCHAR(40) not null,
	ORD_QTY	NUMERIC(18,3) null,
	ORD_AMT	NUMERIC(18,3) null,
	primary key (BAS_YM, CUS_ID, ITM_TP)
);

-- 데이터 삽입
insert into S_CUS_YM (BAS_YM, CUS_ID, ITM_TP, ORD_QTY, ORD_AMT)
select	'201702' as BAS_YM,
		T1.CUS_ID,
		T2.BAS_CD as ITM_TP,
		null as ORD_QTY,
		null as ORD_AMT
from	M_CUS T1,
		C_BAS_CD T2
where 	T2.BAS_CD_DV = 'ITM_TP'
and		T2.LNG_CD = 'KO';

-- 커밋
commit;


-- ************************************************
-- PART I - 4.2.2 SQL2
-- ************************************************

-- 월별고객주문의 주문수량, 주문금액 업데이트
update S_CUS_YM T1
set 
	ORD_QTY = (
		select SUM(B.ORD_QTY)
		from T_ORD A
			join T_ORD_DET B on A.ORD_SEQ = B.ORD_SEQ
			join M_ITM C on C.ITM_ID = B.ITM_ID
		where C.ITM_TP = T1.ITM_TP
		and A.CUS_ID = T1.CUS_ID
		and A.ORD_DT >= TO_DATE(T1.BAS_YM || '01', 'YYYYMMDD')
		and A.ORD_DT < (TO_DATE(T1.BAS_YM || '01', 'YYYYMMDD') + interval '1 month')
	),
	ORD_AMT = (
		select SUM(B.UNT_PRC * B.ORD_QTY)
		from T_ORD A
			join T_ORD_DET B on A.ORD_sEQ = B.ORD_SEQ
			join M_ITM C on C.ITM_ID = B.ITM_ID
		where C.ITM_TP = T1.ITM_TP
		and A.CUS_ID = T1.CUS_ID
		and A.ORD_DT >= TO_DATE(T1.BAS_YM || '01', 'YYYYMMDD')
		and A.ORD_DT < (TO_DATE(T1.BAS_YM || '01', 'YYYYMMDD') + interval '1 month')
	)
where T1.BAS_YM = '201702';


-- ************************************************
-- PART I - 4.2.2 SQL3
-- ************************************************

-- 월별고객주문의 주문금액, 주문수량 업데이트 – 머지 사용
merge into S_CUS_YM T1
using ( -- 서브쿼리
	select	A.CUS_ID,
			C.ITM_TP,
			SUM(B.ORD_QTY) as ORD_QTY,
			SUM(B.UNT_PRC * B.ORD_QTY) as ORD_AMT
	from	T_ORD A
			join T_ORD_DET B on A.ORD_SEQ = B.ORD_SEQ
			join M_ITM C on C.ITM_ID = B.ITM_ID
	where	A.ORD_DT >= TO_DATE('201702' || '01', 'YYYYMMDD')
	and		A.ORD_DT < (TO_DATE('201702' || '01', 'YYYYMMDD') + interval '1 month')
	group by A.CUS_ID, C.ITM_TP
) T2
on (T1.BAS_YM = '201702'
	and T1.CUS_ID = T2.CUS_ID
	and T1.ITM_TP = T2.ITM_TP)
when matched then
	update set	ORD_QTY = T2.ORD_QTY,
				ORD_AMT = T2.ORD_AMT;


-- ************************************************
-- PART I - 4.2.2 SQL4
-- ************************************************

-- 월별고객주문의 주문금액, 주문수량 업데이트 – 반복 서브쿼리 제거
update S_CUS_YM T1
set ORD_QTY = subquery.ORD_QTY,
	ORD_AMT = subquery.ORD_AMT
from (
	select	A.CUS_ID,
			C.ITM_TP,
			SUM(B.ORD_QTY) as ORD_QTY,
			SUM(B.UNT_PRC * B.ORD_QTY) as ORD_AMT
	from T_ORD A
	join T_ORD_DET B on A.ORD_SEQ = B.ORD_SEQ
	join M_ITM C on C.ITM_ID = B.itm_id
	where A.ORD_DT >= TO_DATE('201702' || '01', 'YYYYMMDD')
	  and A.ORD_DT < (TO_DATE('201702' || '01', 'YYYYMMDD') + interval '1 month')
	group by A.CUS_ID, C.ITM_TP
) as subquery
where T1.BAS_YM = '201702'
  and T1.CUS_ID = subquery.cus_id
  and T1.ITM_TP = subquery.ITM_TP;