The MERGE statement (UPSERT, combination of INSERT/UPDATE) was introduced in Oracle9i. In 10g it has been enhanced to include conditional INSERT/UPDATE/DELETE clauses as part of the MERGE statement. Let us use the EMP and BONUS table in SCOTT schema to illustrate the use of this statement EMP Table has got 14 rows SQL> select * from emp; EMPNO ENAME JOB MGR DOJ SAL COMM DEPTNO ----- --------------- ---------- ----- --------- ---------- ---------- ------- 7369 SMITH CLERK 7902 17-DEC-80 1200 20 7499 ALLEN SALESMAN 7698 20-FEB-81 2400 2700 30 7521 WARD SALESMAN 7698 22-FEB-81 1875 2375 30 7566 JONES MANAGER 7839 02-APR-81 4462.5 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1875 3275 30 7698 BLAKE MANAGER 7839 01-MAY-81 4275 30 7782 CLARK MANAGER 7839 09-JUN-81 3675 10 7788 SCOTT ANALYST 7566 19-APR-87 4500 20 7839 KING PRESIDENT 17-NOV-81 7500 10 7844 TURNER SALESMAN 7698 08-SEP-81 2250 2250 30 7876 ADAMS CLERK 7788 23-MAY-87 1650 20 7900 JAMES CLERK 7698 03-DEC-81 1425 30 7902 FORD ANALYST 7566 03-DEC-81 4500 20 7934 MILLER CLERK 7782 23-JAN-82 1950 10 Bonus table has got 5 rows SQL> select * from bonus; ENAME JOB SAL COMM ---------- --------- ---------- ---------- SMITH CLERK 1200 1400 TURNER SALESMAN 2250 1400 MILLER CLERK 1950 1400 MARTIN SALESMAN 1875 1400 JAMES CLERK 1425 1400 Perform a conditional INSERT and UPDATE – an UPSERT If records exist then update the COMM column to COMM*2 If records do not exist then INSERT new records and give them COMM value of 150 SQL> MERGE into bonus b using (select ename, job, sal, comm from emp) s on (b.ename = s.ename) when matched then UPDATE set b.comm = b.comm*2 when not matched then INSERT (b.ename,b.job, b.sal, b.comm) values (s.ename,s.job,s.sal,150); SQL> select * from bonus; ENAME JOB SAL COMM ---------- --------- ---------- ---------- SMITH CLERK 1200 2800 TURNER SALESMAN 2250 2800 MILLER CLERK 1950 2800 MARTIN SALESMAN 1875 2800 JAMES CLERK 1425 2800 BLAKE MANAGER 4275 150 FORD ANALYST 4500 150 ALLEN SALESMAN 2400 150 WARD SALESMAN 1875 150 CLARK MANAGER 3675 150 ADAMS CLERK 1650 150 SCOTT ANALYST 4500 150 KING PRESIDENT 7500 150 JONES MANAGER 4462.5 150 14 rows selected. Note : 5 rows were updated as they already existed 9 new rows were inserted as they did not exist in the BONUS table Records in bold characters are all new records inserted and assigned a default value of 150 Existing records were updated to have the COMM value doubled Perform a conditional UPDATE Management now decides to double the bonus for SALESMAN ….the BONUS table needs to be updated to reflect this change in the EMP table SQL> MERGE into bonus b using (select ename, job, sal, comm from emp where job='SALESMAN') s on (b.ename = s.ename) when matched then UPDATE set b.comm = b.comm*2 when not matched then INSERT (b.ename,b.job, b.sal, b.comm) values (s.ename,s.job,s.sal,s.comm); Note: 4 rows GOT MERGED (updated) SQL> select * from bonus; ENAME JOB SAL COMM ---------- --------- ---------- ---------- SMITH CLERK 1200 2800 TURNER SALESMAN 2250 5600 MILLER CLERK 1950 2800 MARTIN SALESMAN 1875 5600 JAMES CLERK 1425 2800 BLAKE MANAGER 4275 150 FORD ANALYST 4500 150 ALLEN SALESMAN 2400 300 WARD SALESMAN 1875 300 CLARK MANAGER 3675 150 ADAMS CLERK 1650 150 SCOTT ANALYST 4500 150 KING PRESIDENT 7500 150 JONES MANAGER 4462.5 150 14 rows selected. Perform a conditional DELETE Management now decides that the COMM value in the BONUS table needs to be the same as the COMM value in the EMP table. Also, only employees with job title SALESMAN are entitled to a bonus and hence the BONUS table should only have records for the job title SALESMAN SQL> MERGE into bonus b using (select ename, job, sal, comm from emp) s on (b.ename = s.ename) when matched then UPDATE set b.comm = s.comm delete where b.job 'SALESMAN' when not matched then INSERT (b.ename,b.job, b.sal, b.comm) values (s.ename,s.job,s.sal,s.comm); 14 rows merged. SQL> commit; Commit complete. SQL> select * from bonus; ENAME JOB SAL COMM ---------- --------- ---------- ---------- TURNER SALESMAN 2250 2250 MARTIN SALESMAN 1875 3275 ALLEN SALESMAN 2400 2700 WARD SALESMAN 1875 2375
The following two tabs change content below.
Gavin Soorma
