Re: Oracle Merge Statement -sample query

On May 22, 9:10 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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
> 5 );
>
> 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)
> 9 /
>
> 1 row merged.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select *
> 2 from employee
> 3 where id=20;
>
> NAME ID
> SALARY
> -------------------- ----------
> ----------
> BLORPO 20
> 12000
>
> 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)
> 9 /
>
> 1 row merged.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select *
> 2 from employee
> 3 where id=20;
>
> NAME ID
> SALARY
> -------------------- ----------
> ----------
> BLORPO 20
> 22000
>
> SQL>
>
> David Fitzjarrell

Thank you both of you. This was exactly what i was looking for. Btw yes you are right Gints. I meant default for my app. Received on Fri May 23 2008 - 03:30:18 CDT