15일차 # 4-17: Merge (Upsert)

   - deterministic statement: can NOT update the same row multiple times

drop table t1 purge;

drop table t2 purge;

create table t1

as select empno, ename, job, sal from emp

  where rownum <= 8;

create table t2

as select empno, ename, job, sal from emp;

select * from t1;

select * from t2;

update t2 set sal = sal * 1.3 where rownum <= 8;

commit;

select * from t2;

   ------------------------------

begin

 merge into t1

 using t2

 on (t1.empno = t2.empno)

 when matched then

   update set t1.sal = t2.sal

                     , t1.job = t2.job

 when not matched then

   insert (t1.empno, t1.ename, t1.sal)

   values (t2.empno, t2.ename, t2.sal*1.1);

end;

/

   ------------------------------

select * from t1;

   ------------------------------

* 10g부터는 when matched then delete도 가능