update 1 table from multiple tables

Have you ever wanted to update a table based upon a join.  For example, wanted to update F00165 based upon a join of F00165 and another table?  This is really hard to talk about generically, so lets get specific.

I have a table F00165 with all my media objects.  I have another table F00165SRM that has the location of all of the NEW media objects that exist.  So now I want to update F00165 and set the gdgtfilenm to equal the new value.

Oracle does not support joins in update statements.  I was trying to do a complex where exists type query, but it was taking too long.

I can query the results easy enough, but hard to turn this into an update statement:

select replace(mofilename, 'Z:\','\\jdedatastore\') , substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
    from  proddta.f00165srm, proddta.f00165
    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 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)))
    and length(trim(gdgtfilenm)) > 50;

So I stumble on the MERGE statement, and this makes me very happy!

  MERGE
  INTO    proddta.f00165 t3
  USING   (
          SELECT  t1.rowid AS rid, replace(t2.mofilename, 'Z:\','\\jdedatastore\') 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;

 

As you can see from the above this seems to be a nice and simple way of doing the update of a single table with the join of multiple tables.  The other nice thing is that you can also do WHEN NOT MATCHED and apply some other logic.  See oracle doco here http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606 

I do however get an ORA-04036 when running it – so perhaps there is a little fine tuning required.  Nice new 12C feature that it hitting me.