1

This merge statement raise ORA-00905:missing keyword exception. What can be the problem? Something with the WHEN MATCHED branch?

MERGE INTO WORKERPROJECT TARGET
USING (SELECT distinct
               w.worker_id,
               w.worker_type,
               w.project_id worker_project_id, 
               p.project_id project_project_id,
               p.dedicated_project_leader,
               p.dedicated_lead_developer,
               p.dedicated_lead_consultant,
               p.dedicated_supervisor
       from WORKER w 
       join PROJECT p on w.project_id = p.project_id
      ) SOURCE
   ON (TARGET.FK_worker_id=SOURCE.WORKER_ID)
 WHEN MATCHED THEN INSERT (TARGET.FK_WORKER_ID, TARGET.FK_PROJECT_ID,TARGET.IS_ACTIVE,POSITION) 
  VALUES (SOURCE.WORKER_ID,SOURCE.worker_project_id,'IS_ACTIVE',SOURCE.worker_type);
Robert
  • 25,425
  • 8
  • 67
  • 81
czupe
  • 4,740
  • 7
  • 34
  • 52
  • 1
    Can you do an insert on WHEN MATCHED? I thought you could only do an insert on WHEN NOT MATCHED. http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php – twoleggedhorse Jan 22 '13 at 09:45
  • good question.Yeah maybe you have right, i try it... Anyway --> http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm – czupe Jan 22 '13 at 09:47
  • Sick, it is ok now. Please write the answer and i will vote up + accept it! I dunno why is this way, but i negate the on condition and it will be OK. – czupe Jan 22 '13 at 09:49
  • Think about it: why would you want to *insert* if the row is already there? (*when matched*). –  Jan 22 '13 at 09:54
  • true but i have a little weird merge, i will show it in an other question – czupe Jan 22 '13 at 10:00

1 Answers1

2

According to http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

You can only do an insert on WHEN NOT MATCHED.

twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38