using MERGE statement and getting ORA-30926: unable to get a stable set of rows in the source tables

This was a pain, but at the end of the day quite simple to solve.  I had a large MERGE statement (I’d moved media objects to separate folders based upon change date) and needed to update these in bulk in the F00165 (2 million + attachments!)

My merge statement kept failing with the error above and I was scratching my head about what I’d done wrong.  I used a script to populate F00165SRM (as seen in previous post) and then I used this as my master list to update the F00165 from.  It was nice and simple.  What I did not realise is that in pursuit of performance (damn me), I multithreaded the cr@p out of the script that populated F00165SRM, I launched a heap of them to try and catch up (under the pump again), I’d inadvertently launched a script twice, so I had a shed load of dupes in F00165SRM.  this is an “essentially insert only” table, so I did not want to slow things down with uniqueness based PKs!  So the reason I was getting ORA-30926 was because I had heaps of duplicates in the F00165SRM! 

A quick script to deduplicate the table: (based upon)

 select count(1), mofilename
  from proddta.f00165srm
  group by mofilename
  having count(1) > 1 ;
  
  delete from proddta.f00165srm
where (rowid, mofilename) in (
select max(rowid), mofilename
from proddta.f00165srm
group by mofilename
having count(1) > 1);
commit;

First one to identify and second to delete (500000 rows – whoops).  I was then able to run the merge successfully:

MERGE
  INTO    proddta.f00165 t3
  USING   (
          SELECT  t1.rowid AS rid, replace(t2.mofilename, 'Z:\','\\jdedatastore\JDEMediaObjects\MediaObjectsData\') as newMOLocation
          FROM    proddta.f00165 t1
          JOIN    proddta.f00165srm t2
          ON      substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)))
          WHERE   instr(mofilename,'\',-1,1) > 1
            and instr(gdgtfilenm,'\',-1,1) > 1
            and  length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 5
            and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 5       
            and  gdgtmotype = '5')      
  ON      (t3.rowid = rid)
  WHEN MATCHED THEN
  UPDATE
  SET     t3.gdgtfilenm = newMOLocation;
  commit;

That is very nice.

My statement is called an UPSERT – how cool is that!

Closing the loop on this:

Once again I had the ORA-30926 after cleaning up what I thought were all the duplicates, but there were more.

What I found was that you need to use the “JOIN” clause for the source table, this needs to be unique.  Therefore the real test of duplicates was

  select count(1), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
  from proddta.f00165srm
  group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
  having count(1) > 1 ;

 

Notice how I’m not checking the entire field, just the bit that my JOIN was joining on.  I was then able to identify all of the offending records and delete them from the F00165SRM.  Which this is once again a slightly more complicated version of the above example of de-duplication.

DELETE from proddta.F00165SRM 
where (rowid, substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))) in (
select min(rowid), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
from proddta.f00165srm
group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
having count(1) > 1);