 | |
Oracle Merge Statement -sample query
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Oracle Merge Statement -sample query -
05-22-2008
, 11:10 AM
|
On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: http://download.oracle.com/docs/cd/B.../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> 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
#22
| |||
| |||
|
|
On May 22, 2:25 am, idey <dey.indra... (AT) gmail (DOT) com> wrote: On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: http://download.oracle.com/docs/cd/B.../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 |
yes you are right Gints. I meant default for my app.
#23
| |||
| |||
|
|
On May 22, 2:25 am, idey <dey.indra... (AT) gmail (DOT) com> wrote: On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: http://download.oracle.com/docs/cd/B.../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 |
yes you are right Gints. I meant default for my app.
#24
| |||
| |||
|
|
On May 22, 2:25 am, idey <dey.indra... (AT) gmail (DOT) com> wrote: On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: http://download.oracle.com/docs/cd/B.../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 |
yes you are right Gints. I meant default for my app.
#25
| |||
| |||
|
|
On May 22, 2:25 am, idey <dey.indra... (AT) gmail (DOT) com> wrote: On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com wrote: http://download.oracle.com/docs/cd/B.../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 |
yes you are right Gints. I meant default for my app.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.