Merge Into 구문

▶ Sample 1 |  

MERGE INTO YT_PRODUCT yp
USING dual
ON (yp.model like '갤럭시Ultra%')
WHEN MATCHED THEN
  UPDATE SET Price_Sale = 1400000 ,  Price_buy = 1300000, modi_day=sysdate 
  WHERE yp.model = '갤럭시Ultra21'
WHEN NOT MATCHED THEN
      INSERT (PRD_ID, PRD_NM, SPEC, MAKER, MODEL, 
                              PRICE_SALE, PRICE_BUY, REG_DAY, MODI_DAY)
      VALUES ('P'||lpad(SQ_YT_CUSTOMER_PRODUCTID.nextval,9,'0'), 
                 '스마트폰' , '울트라폰', '삼성', '갤럭시Ultra21', 1400000, 1300000, sysdate, null)
                 ;

▶ Sample 2 |  

MERGE INTO YT_PRODUCT yp
USING (select x.prd_id from yt_order_info x where ord_no='J000000205') x 
ON (yp.model like '갤럭시Ultra%' and yp.prd_id=x.prd_id)
WHEN MATCHED THEN
  UPDATE SET Price_Sale = 1400000 ,  Price_buy = 1300000, modi_day=sysdate 
  WHERE yp.model = '갤럭시Ultra21'
WHEN NOT MATCHED THEN
      INSERT (PRD_ID, PRD_NM, SPEC, MAKER, MODEL, 
                              PRICE_SALE, PRICE_BUY, REG_DAY, MODI_DAY)
      VALUES ('P'||lpad(SQ_YT_CUSTOMER_PRODUCTID.nextval,9,'0'), 
                 '스마트폰' , '울트라폰', '삼성', '갤럭시Ultra21', 1400000, 1300000, sysdate, null)
                 ;

---

▶ Sample 3 |  

** 기본 예제 **

1.
MERGE INTO emp_test et
USING emp e
ON(et.empno = e.empno)
WHEN MATCHED THEN
UPDATE SET et.sal = e.sal*1.1
WHEN NOT MATCHED THEN
INSERT VALUES (e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno)



2.
MERGE INTO TSEA_BKG_BLNO A
USING DUAL
ON(A.HBL_NO = 'ULSOD08050001')
WHEN MATCHED THEN
UPDATE SET A.BL_CREATE_YN = 'N'
WHEN NOT MATCHED THEN
INSERT VALUES( 'BKSOD08050001', 'ULSOD08050006', 'UNICO', 'Y')


merge into estimate_log a
using dual
on (a.ndx = 1)
when matched then
update set a.estimate_ndx_array = a.estimate_ndx_array || '/' || '129' where a.ndx = 1
when not matched then
insert (ndx, doc_ndx, regist_date, estimate_ndx_array)
values (1, 1000049000, sysdate, '128')

---