Oracle merge command error missing keyword

0
votes

I am trying to merge two tables in oracle and the query i am using is 

merge into sales_history sh
 using sales s
 on (s.prod=sh.prod and s.month=sh.month)
 when matched 
      update set sh.amount = s.amount
 when not matched 
      insert (sh.prod,sh.month,sh.amount)
      values (s.prod,s.month,s.amount);

Whenever I execute this query i get the following error:

ORA-00905 missing keyword.

Can anyone help me with this.

1
sqloraclesql-mergeora-00905
when matched THEN update .... when not matched THEN ... – a_horse_with_no_name
yeah i spotted the error but thank you so much for answering :) – daniyal.bashir

1 Answers

1
votes
 when matched 
      update set sh.amount = s.amount
 when not matched 
      insert (sh.prod,sh.month,sh.amount)

Your MERGE syntax is incorrect. You are missing the THEN keyword.

From the documentation:

merge_update_clause ::=

WHEN MATCHED THEN
   UPDATE SET ...

merge_insert_clause ::=

WHEN NOT MATCHED THEN
   INSERT