-1

Possible Duplicate:
ORA-30926: unable to get a stable set of rows in the source tables

I am trying to execute the following merge statement but is is showing me ora-30926 error

merge into test_output target_table 
USING (SELECT c.test_code, 
  c.v_report_id, upper_score,
        CASE WHEN c.test_code = 1 THEN b.mean_diff 
             WHEN c.test_code = 2 THEN b.norm_dist 
             WHEN c.test_code = 3 THEN b.ks_stats 
             WHEN c.test_code = 4 THEN b.ginni 
             WHEN c.test_code = 5 THEN b.auroc 
             WHEN c.test_code = 6 THEN b.info_stats 
             WHEN c.test_code = 7 THEN b.kl_stats 
                    END val1 
     FROM   combined_approach b inner join test_output c
          on  b.v_report_id = c.v_report_id 
          and c.upper_score = b.band_code 
             WHERE c.v_report_id = lv_report_id  
                        ORDER  BY c.test_code) source_table
                        on(target_table.v_report_id = source_table.v_report_id
                        and  target_table.v_report_id = lv_report_id)
                when matched then         
update  SET    target_table.upper_value = source_table.val1;
Community
  • 1
  • 1
user1331181
  • 1
  • 3
    http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables – Mat Apr 14 '12 at 09:51

1 Answers1

0

I think you have this error because you have rows having the same v_report_id on the using clause Could you try this 

merge into test_output target_table 
USING (SELECT 
        CASE WHEN c.test_code = 1 THEN b.mean_diff 
             WHEN c.test_code = 2 THEN b.norm_dist 
             WHEN c.test_code = 3 THEN b.ks_stats 
             WHEN c.test_code = 4 THEN b.ginni 
             WHEN c.test_code = 5 THEN b.auroc 
             WHEN c.test_code = 6 THEN b.info_stats 
             WHEN c.test_code = 7 THEN b.kl_stats 
                    END val1 
     FROM   combined_approach b inner join test_output c
          on  b.v_report_id = c.v_report_id 
          and c.upper_score = b.band_code 
             WHERE c.v_report_id = lv_report_id  
              group by v_report_id          
) source_table
on (target_table.v_report_id = source_table.v_report_id
    and  target_table.v_report_id = lv_report_id)
when matched then update  
   SET   target_table.upper_value = source_table.val1;
bijgaa
  • 1