Forum Stats

  • 3,824,449 Users
  • 2,260,369 Discussions
  • 7,896,190 Comments

Discussions

hi experts can we delete record using merge statement

User_8GUKB
User_8GUKB Member Posts: 2 Green Ribbon

MERGE INTO emp_2 e2

USING emp_1 e1

ON(e1.emp_id=e2.emp_id)

WHEN MATCHED THEN 

UPDATE SET e2.emp_name=e1.emp_name

WHEN NOT MATCHED THEN

INSERT(e2.emp_id,e2.emp_name)VALUES(e1.emp_id,e1.emp_name)

WHEN NOT MATCHED BY e2 THEN 

DELETE;


I faced below error

WHEN NOT MATCHED BY e2 THEN

         *

ERROR at line 8:

ORA-00905: missing keyword

how i overcome it ,please give a solution

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,452 Red Diamond
    Answer ✓

    Merge updates/deletes matching rows and/or inserts non-matching rows. When matched means we took source row (source is defined by USING clause) and found a matching row in target (defined by INTO clause). In such case we can update/delete target row but not insert like in your example. When not matched means we took source row (source is defined by USING clause) but didn't find a matching row in target (defined by INTO clause). So what's here to delete from target?

    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,452 Red Diamond
    Answer ✓

    Merge updates/deletes matching rows and/or inserts non-matching rows. When matched means we took source row (source is defined by USING clause) and found a matching row in target (defined by INTO clause). In such case we can update/delete target row but not insert like in your example. When not matched means we took source row (source is defined by USING clause) but didn't find a matching row in target (defined by INTO clause). So what's here to delete from target?

    SY.

  • User_8GUKB
    User_8GUKB Member Posts: 2 Green Ribbon

    thank you for your clarity