Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

why won't this MERGE statement work?

C PatelOct 15 2007 — edited Oct 16 2007

I am testing out a MERGE statement, which contains a subquery that will return no rows...on this condition, it should insert a record; however I get 0 records merged. Why is this?
Ultimately, the hard-coded values will actually be passed in as parameters. If those a record with those four values is not found, then it should insert.

MERGE INTO tb_slea_election_worksheet A
USING  
(SELECT i.slea_year,i.empid,i.bda_sum_sort,i.bda_dtl_sort FROM tb_slea_election_worksheet i 
WHERE  slea_year = '2008'
AND empid = '6468T'
AND bda_sum_sort = '30'
AND bda_dtl_sort = '9999'
) B
ON (A.slea_year = B.slea_year
AND A.empid =B.empid
AND A.bda_sum_sort  = B.bda_sum_sort
AND A.bda_dtl_sort  = B.bda_dtl_sort)  
WHEN MATCHED THEN
  UPDATE SET A.fa_proj_exp_amt  = 888
WHEN NOT MATCHED THEN
  INSERT    (slea_year,empid,bda_sum_sort,bda_dtl_sort,act_exp_lst_yr,fa_proj_exp_amt)
  VALUES ( '2008','6468T','30','9999','0','55');
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 13 2007
Added on Oct 15 2007
25 comments
1,037 views