Re: Oracle Merge Statement -sample query

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. Received on Thu May 22 2008 - 02:25:06 CDT