Howard J. Rogers wrote:
> 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
And (c) of course should have read 'doesn't work when abused as you
propose'.
Regards
HJR
Received on Sat Dec 11 2004 - 01:47:20 CST
