13일차 # 3-29: MERGE 예제

        drop table emp1 purge;

create table emp1

as select empno, ename, job, sal, deptno

from emp

where rownum <= 5;

drop table emp2 purge;

create table emp2

as select empno, ename, job, sal*1.1 as sal

from emp;

select * from emp1;

select * from emp2;

merge into emp1 e1

 using emp2 e2

 on (e1.empno = e2.empno)

when matched then

 update set

   e1.ename = e2.ename,

   e1.job = e2.job,

   e1.sal = e2.sal,

   e1.deptno = to_number(null)

when not matched then

 insert values

   (e2.empno, e2.ename, e2.job, e2.sal, to_number(null));

select * from emp1;