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 .
