Re: Oracle Merge Statement -sample query

On May 22, 2:25 am, idey <dey.indra..._at_gmail.com> wrote:
> On May 21, 5:31 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
>
> >http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statem...
>
> > --
>
> > Regards,
> > Frank van Bortel
>
> Hi All,
> Thanks for your replies. But I think you missed my point. The query I
> am doing is not a school homework but quite a complex one and since I
> have never used a Merge statement before I  have sought your help with
> an example.
>
> The problem with Merge I think is that its trying to merge from a
> source table/ view/ dataset into the taget table. My problem is when I
> have a single table to work on i.e. source and target tables are same.
> In this case the predicate inside the using clause filters all records
> so no rows are returned.
> If I do not use the predicate then there is a different problem
> altogether.
>
> --with predicate filter in using
> MERGE INTO EMPLOYEE E
>    USING (SELECT D.ID, D.NAME, D.SALARY FROM employee D
>    WHERE D.ID = 80) S
>    ON (E.ID = S.ID)
>    WHEN MATCHED THEN UPDATE SET E.SALARY = S.SALARY + 10000
>    WHEN NOT MATCHED THEN INSERT (E.ID, E.NAME, E.SALARY)
>      VALUES (80,'ENAME',20000);
>
> --without preidicate filter in using
> MERGE INTO EMPLOYEE E
>    USING (SELECT D.ID, D.NAME, D.SALARY FROM employee D) S
>    ON (E.ID = S.ID)
>    WHEN MATCHED THEN UPDATE SET E.SALARY = S.SALARY + 10000
>    WHEN NOT MATCHED THEN INSERT (E.ID, E.NAME, E.SALARY)
>      VALUES (80,'ENAME',20000)
>          where S.id=80;
>
> Both of them do not work.
>
> There are other use cases when i use either when the record exists or
> not and that doesnt seem to give the expected results either. I would
> not want to bother you buys with them here unless you want my full
> analysis too.
>
> Kindly point out if i have written the query wrong :)
>
> Thanks,
> Indranil.

SQL>
SQL> create table employee (

  2  	     name varchar2(20),
  3  	     id  number primary key,
  4  	     salary number

Table created.

SQL>
SQL> insert into employee
  2 select ename, empno, sal
  3 from emp;

14 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2 from employee
  3 where id=20;

no rows selected

SQL>
SQL> merge into employee e
  2 using (select 20 as id from dual) i on (e.id = i.id)   3 when matched then

  4  	     update
  5  	     set salary = salary + 10000
  6  when not matched then
  7  	     insert
  8  	     values('BLORPO',20,12000)

1 row merged.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2 from employee
  3 where id=20;

NAME                         ID

-------------------- ----------
----------
BLORPO                       20

SQL>
SQL> merge into employee e
  2 using (select 20 as id from dual) i on (e.id = i.id)   3 when matched then

  4  	     update
  5  	     set salary = salary + 10000
  6  when not matched then
  7  	     insert
  8  	     values('BLORPO',20,12000)

1 row merged.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2 from employee
  3 where id=20;

NAME                         ID

-------------------- ----------
----------
BLORPO                       20

SQL> David Fitzjarrell Received on Thu May 22 2008 - 11:10:10 CDT