Hi All,
I have a simple question but i am in a fix coz of it.
This is regarding Oracle Merge statement.

The question is:

I have a table say

Employee
- Name varchar2(20)
- ID pk number
- salary number

I want to check whether ID = 20 exists or not. If yes, then update
salary = salary +10000;
else insert the row with default values.

Can someone give me the SQL for this? I have tried all the seemingly
normal approaches.

Regards,
Indranil.

On May 20, 4:59*am, idey <dey.indra... (AT) gmail (DOT) com> wrote:
Then show us what you've tried, as this appears to be an assignment
for school. We won't do your homework for you , but we WILL offer
assistance when we see what you've tried.


David Fitzjarrell

On May 20, 4:59*am, idey <dey.indra... (AT) gmail (DOT) com> wrote:
Then show us what you've tried, as this appears to be an assignment
for school. We won't do your homework for you , but we WILL offer
assistance when we see what you've tried.


David Fitzjarrell

On May 20, 4:59*am, idey <dey.indra... (AT) gmail (DOT) com> wrote:
Then show us what you've tried, as this appears to be an assignment
for school. We won't do your homework for you , but we WILL offer
assistance when we see what you've tried.


David Fitzjarrell

On May 20, 4:59*am, idey <dey.indra... (AT) gmail (DOT) com> wrote:
Then show us what you've tried, as this appears to be an assignment
for school. We won't do your homework for you , but we WILL offer
assistance when we see what you've tried.


David Fitzjarrell

http://download.oracle.com/docs/cd/B...htm#SQLRF01606

--

Regards,
Frank van Bortel

http://download.oracle.com/docs/cd/B...htm#SQLRF01606

--

Regards,
Frank van Bortel

http://download.oracle.com/docs/cd/B...htm#SQLRF01606

--

Regards,
Frank van Bortel

http://download.oracle.com/docs/cd/B...htm#SQLRF01606

--

Regards,
Frank van Bortel

On May 21, 5:31 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
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.