Home » SQL & PL/SQL » SQL & PL/SQL » Merge Update - ORA 30926 (Oracle 10g)
Merge Update - ORA 30926 [message #298824] Thu, 07 February 2008 20:48 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi,
I'm trying to update using merge statement but i had error message

MERGE INTO PAYMENT PAY            
USING 
(SELECT distinct T3.generic_acct generic_acct                         
,T1.rowid row_id                    
FROM PAYMENT T1                     
,REVENUE_CODE T2                        
,REV_GEN_POST T3                   
         WHERE T1.npcs_file_seq_num = :SeqNum                      
          AND T1.rev_code = T2.rev_code                   
          AND T2.posting_code = '2'                       
          AND T1.rev_code = T3.rev_code                   
         AND T1.txn_date >= T3.eff_date                  
         AND (T3.term_date is null OR T1.txn_date <= T3.term_date)) GEN        
           ON (PAY.rowid = GEN.row_id)             
         WHEN MATCHED THEN                      
           UPDATE SET PAY.acct_no = GEN.generic_acct
   
         ORA-30926: unable to get a stable set of rows in the source tables
          PROCESS ABORTED


From what i've read, we can get this error if the select statement return duplicate rows. So i put the distinct. But i still got the error when i pass other T1.npcs_file_seq_num .
Is is because or merge update?
Please advise.
Thanks.
Re: Merge Update - ORA 30926 [message #298827 is a reply to message #298824] Thu, 07 February 2008 21:54 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
distinct will have no effect .use group by your in subquery to remove given error.

regards,


Re: Merge Update - ORA 30926 [message #298833 is a reply to message #298824] Thu, 07 February 2008 23:11 Go to previous message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-30926: unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.
Action: Remove any non-deterministic where clauses and reissue the dml.

Regards
Michel
Previous Topic: ORA-02292: integrity constraint - violated - child record
Next Topic: ora-04091:table user.table1 is mutating,trigger function may not see
Goto Forum:
  


Current Time: Thu Dec 08 06:20:01 CST 2016

Total time taken to generate the page: 0.21555 seconds