MERGE INTO table_name alias --테이블 명만 올수 있음(셀렉트 쿼리 No)
USING (table|view|subquery) alias --일반적으로 쓰는 쿼리 Ok
ON (join condition) --where 절이라고 생각하면됨(조건에 서브 OK)
WHEN MATCHED THEN --데이터 중복건이 있는경우 Update
UPDATE SET col1 = val1[, col2 = val2…]
WHEN NOT MATCHED THEN -- 중복건이 없는 경우 처리 Insert
INSERT (컬럼리스트) VALUES (값들....);
Ex 1.
MERGE INTO TABLE_NAME1 B
USING (SELECT B.AA, A.* FROM TABLE_NAME2 A, TABLE_NAME3 B
WHERE A.BB = B.BB(+)) A
ON (B.CC= A.CC AND B.AC IS NULL AND DD > SYSDATE - 24)
WHEN MATCHED THEN
UPDATE SET A = A.A,
B = A.B;
Ex 2.
MERGE INTO CRCD_WF_CARD A
USING DUAL
ON ( A.WF_NO = ? AND A.CRD_NO =? )
WHEN MATCHED THEN
UPDATE SET
A.CRD_TYPE =?
, A.FML_SSN =?
, A.FMLHG_NM =?
, A.FMLENG_NM =?
WHEN NOT MATCHED THEN
INSERT (
A.WF_NO
, A.CRD_NO
, A.CRD_TYPE
, A.FML_SSN
, A.FMLHG_NM
, A.FMLENG_NM
) VALUES ( ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ? )
Ex 3.
MERGE INTO TC_CUSTAGENT A
USING (SELECT BUSI_CODE, CUST_CODE, CHPE_NAME, CUST_PHNB, CUST_FANU
FROM TB_ESTMAST
WHERE BUSI_CODE = '___parameter___'
AND CUST_CODE = '____parameter___') B
ON (A.BUSI_CODE = B.BUSI_CODE
AND A.AFON_COMP = B.CUST_CODE
AND A.SEQU_NUMB = (SELECT MAX(SEQU_NUMB)
FROM TC_CUSTAGENT
WHERE BUSI_CODE = A.BUSI_CODE
AND AFON_COMP = A.AFON_COMP))
WHEN MATCHED THEN
UPDATE
SET A.CHPE_NAME = B.CHPE_NAME
,A.CABL_PHNB = B.CUST_PHNB
,A.FAXM_NUMB = B.CUST_FANU
WHEN NOT MATCHED THEN
INSERT ( A.AFON_COMP
,A.CHPE_NAME
,A.CABL_PHNB
,A.FAXM_NUMB
)
VALUES ( B.CUST_CODE
,B.CHPE_NAME
,B.CUST_PHNB
,B.CUST_FANU
);