Using Merge command

[debasisdas]

Using Merge command (supported version 9 onwards)
=============== =============== ==========
Used to go for insert/update in a single command based on some condition.

Example #1

merge into emp5 e
using (select empno,ename from emp) e1
on (e.empno=e1.empno)
when matched then
update set
e.ename=e1.ename
 when not matched then
insert values(e1.empno,e1.ename)
 

If the empno exists then update else insert the record.

Example#2

 
merge into empls e
using emp e1
on (e.empno=e1.empno)
when matched then
update set
e.ename=e1.ename,
e.job=e1.job,
e.mgr=e1.mgr,
e.hiredate=e1.hiredate,
e.sal=e1.sal,
e.comm=e1.comm,
e.deptno=e1.deptno
when not matched then
insert values(e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno)
 

Just another simple example .