Re: How to use merge not to do an upsert but to do a simple update

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 11 Dec 2004 16:44:46 +1100
Message-ID: <41ba8955$0$7078$afc38c87@news.optusnet.com.au>

chirantan.chakraborty_at_gmail.com wrote:
> Hi All,
>
> Although the main purpose of using MERGE in Oracle 9i is to do UPSERT,
> but I somehow want to find out if it is at all possible for us to do a
> plain UPDATE with it instead.
>
> I know I can use a CURSOR to do this, but I would like to experiment
> with MERGE.
> ==============================================================
> MERGE INTO SERVICE_TYPE_DIM A
> USING SERVICE_TYPE_DIM_BK B
> on
> (
> A.SERVICE_TYPE_CDE = B.SERVICE_TYPE_CDE
> )
> WHEN MATCHED THEN UPDATE
> SET
> A.SERVICE_TYPE_DIM_KEY = B.SERVICE_TYPE_DIM_KEY
> WHEN NOT MATCHED THEN
> -- do not want to insert anything
> INSERT()
> VALUES
> (
> )

The logic of this escapes me. "If matched, then update, else nothing" is exactly the same as saying "Update if some condition is met".

Consider this example where I set the EMP salaries to be the same as a backup table's salaries provided it's for Department 10:

SQL> connect scott/tiger

SQL> select empno, ename, sal, deptno from EMP order by deptno;

      EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ----------

       7782 CLARK            2450         10
       7839 KING             5000         10
       7934 MILLER           1300         10
       7369 SMITH             800         20
       7876 ADAMS            1100         20
       7902 FORD             3000         20

SQL> create table empcopy as select * from emp;

SQL> update empcopy set sal=sal*2.25;
SQL> commit;

select empno, ename, sal, deptno from EMPCOPY order by deptno;

      EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ----------

       7782 CLARK          5512.5         10
       7839 KING            11250         10
       7934 MILLER           2925         10
       7369 SMITH            1800         20
       7876 ADAMS            2475         20
       7902 FORD             6750         20

SQL> update EMP e set sal=(select empcopy.sal from empcopy where e.empno=empcopy.empno) where deptno=10;

3 rows updated.

SQL> select empno, ename, sal, deptno from EMP order by deptno;

      EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ----------

       7782 CLARK          5512.5         10
       7839 KING            11250         10
       7934 MILLER           2925         10
       7369 SMITH             800         20
       7876 ADAMS            1100         20
       7902 FORD             3000         20

And the final result is a partial update of the EMP table, using values in another table as the updating value. And no need to stuff around with a MERGE statement that is (a) only available in 9i and above and (b) not intended to do what you're doing with it! and (c) works!!

Regards
HJR Received on Fri Dec 10 2004 - 23:44:46 CST