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

Usually we will get "ORA-30926: unable to get a stable set of rows in the source tables" error when we are working with MERGE statement, especially when we use same table for both target and source tables.

SQL> merge into merge_test mt
  2  using merge_test mt1
  3  on( mt.npa= mt1.npa)
  4  when matched then
  5    update set mt.m_end  = mt1.m_end
  6    delete  where mt.m_start= mt1.m_end
  7  ;

merge into merge_test mt
using merge_test mt1
on( mt.npa= mt1.npa)
when matched then
  update set mt.m_end  = mt1.m_end
  delete  where mt.m_start= mt1.m_end

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

we are getting this error as its trying to fetch records from same row(it means same rowid) and to differentiate we need to use rowid as below.


SQL> 
  2  merge into merge_test mt
  3  using merge_test mt1
  4  on( mt.npa= mt1.npa and mt.rowid <> mt1.rowid)
  5  when matched then
  6    update set mt.m_end  = mt1.m_end
  7    delete  where mt.m_start= mt1.m_end
  8  ;


Done