Merge statement generate exception, but why

1
votes

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);
1
sqloraclemergemerge-statement
Can you do an insert on WHEN MATCHED? I thought you could only do an insert on WHEN NOT MATCHED. oracle-base.com/articles/10g/merge-enhancements-10g.php – twoleggedhorse
good question.Yeah maybe you have right, i try it... Anyway --> docs.oracle.com/cd/B19306_01/server.102/b14200/… – czupe
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
Think about it: why would you want to insert if the row is already there? (when matched). – a_horse_with_no_name
true but i have a little weird merge, i will show it in an other question – czupe

1 Answers

2
votes

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

You can only do an insert on WHEN NOT MATCHED.