MSSQL과 ORACLE의 MERGE 구문 비교

같은듯 다른

MSSQL과 ORACLE의

MERGE 구문 비교

<Merge 테이블>

icki_base_tmp 테이블데이터를

icki_base 테이블에 merge 할 경우,

1) MSSQL

 merge into icki_base a

 using icki_base_tmp b

    on (a.c_no = b.c_no

and a.cm_acct_no = b.cm_acct_no)

  when matched and a.cr_acid_no = '1' then

update set ct_id_no = b.ct_id_no

         , cr_acid_no = b.cr_acid_no

  when matched and a.cr_acid_no = '2' then

delete

  when not matched then

insert (c_no, cm_acct_no, ct_id_no, cr_acid_no)

 values (b.c_no, b.cm_acct_no, b.ct_id_no, b.cr_acid_no);

2) Oracle

 merge into icki_base a

 using icki_base_tmp b

    on (a.c_no = b.c_no

and a.cm_acct_no = b.cm_acct_no) -- 괄호가 없으면 인식 잘 못함

  when matched then

update set ct_id_no = b.ct_id_no

         , cr_acid_no = b.cr_acid_no

 where a.cntr_acct_id_no = '1'

 delete

 where a.cntr_acct_id_no = '2'

  when not matched then

insert (c_no, cm_acct_no, ct_id_no, cr_acid_no)

 values (b.c_no, b.cm_acct_no, b.ct_id_no, b.cr_acid_no);