Home » SQL & PL/SQL » SQL & PL/SQL » MERGE statement: Possible to use DELETE and INSERT only?
MERGE statement: Possible to use DELETE and INSERT only? [message #277939] Thu, 01 November 2007 05:09 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi all,

While using merge statement can we only use delete and insert instead update and insert.

My problem is when i try to delete the matching records and to insert the non- matching records im getting the following error.

  1  BEGIN
  2  MERGE INTO merge1 m1
  3      USING (SELECT a,b,c FROM merge) e
  4      ON (m1.a = e.a)
  5      WHEN MATCHED THEN
  6     delete where m1.c = e.c
  7      WHEN NOT MATCHED THEN
  8        INSERT (m1.a, m1.b)
  9        VALUES (e.a, e.b);
 10  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
 11* END;
SQL> /
   delete where m1.c = e.c
   *
ERROR at line 6:
ORA-06550: line 6, column 4:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored


can someone help me in this regard...

fortune
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277955 is a reply to message #277939] Thu, 01 November 2007 05:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Did you forget to put ()'s around the condition after DELETE?
Have a look at MERGE.

By
Vamsi
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277958 is a reply to message #277939] Thu, 01 November 2007 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you look at the syntax diagram, you see that delete can only be given if update is given.
This could be a dummy update, set a=a for instance.

Regards
Michel
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277966 is a reply to message #277939] Thu, 01 November 2007 05:49 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

But i have seen somewhere using delete only, i think we can use it. Syntax with which i am using might be gone wrong.

BEGIN
MERGE INTO merge1 m1
    USING (SELECT a,b,c FROM merge) e
    ON (m1.a = e.a)
    WHEN MATCHED THEN 
   delete 
    WHEN NOT MATCHED THEN
      INSERT (m1.a, m1.b)
      VALUES (e.a, e.b);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
END;


Here in this code i have used only delete without update. is it correct or where do i need to modify?

Help me out...
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277968 is a reply to message #277966] Thu, 01 November 2007 05:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what happens if you run it?
That should be your first step, not asking here what will happen!
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277969 is a reply to message #277939] Thu, 01 November 2007 05:55 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

I did and got keyword missing error!
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277975 is a reply to message #277969] Thu, 01 November 2007 06:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.
I missed it, but Michel pointed out.
Check Michel's post and put UPDATE clause also.

By
Vamsi
Re: MERGE statement: Possible to use DELETE and INSERT only? [message #277995 is a reply to message #277975] Thu, 01 November 2007 07:00 Go to previous message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Thanks a lot!
Previous Topic: how to insert ?
Next Topic: LOB data type variables...
Goto Forum:
  


Current Time: Sun Dec 11 04:15:23 CST 2016

Total time taken to generate the page: 0.13517 seconds