title: Oracle SQL - How do I update from an Outer Joined Table?
body: <p><strong>The Problem</strong></p>
  
  <p>I need to write an Update query where my SET references an outer joined table.</p>
  
  <p>I can do this fairly easily with SQL Server but I'm having a heck of a time figuring out the syntax in Oracle as I'm only allow a single table in an update query.</p>
  
  <p>I have written the following Oracle query:</p>
  
  <pre><code>UPDATE SalesExt_tmp tmp
  SET slsrep = (SELECT three_dig_rep 
                FROM dw_sls_rep_conv sls
                WHERE sls.aims_rep = tmp.slsrep)
  WHERE EXISTS (SELECT three_dig_rep
                FROM dw_sls_rep_conv sls
                WHERE sls.aims_rep = tmp.slsrep)
            AND tmp.sysind = 'AIM';
  </code></pre>
  
  <p>This takes care of the intersection but I need to deal with values in SalesExt_tmp that do not have equivalent matches in dw_sls_rep_conv (I plan to add a case statement to set null values to a default value). To do this I need to set up dw_sls_rep_conv as an outer joined table. But this is where I get stuck.</p>
  
  <p><strong>SQL Server Example</strong></p>
  
  <p>In SQL Server the solution is a piece of cake as you can have multiple tables in an Update Query:</p>
  
  <pre><code>UPDATE SalesExt_tmp tmp 
  LEFT JOIN dw_sls_rep_conv sls ON sls.aims_rep = tmp.slsrep 
  SET tmp.slsrep = sls.three_dig_rep
  WHERE tmp.sysind = 'AIM';
  </code></pre>
  
  <p>But I can't for the life of me figure out how to do this in Oracle. I understand that this query will allow my slsrep field to be set to NULL in some occasions which causes me to fear that this operation may not be allowed. </p>
  
  <p><strong>Questions</strong></p>
  
  <p>1) Firstly is this possible in Oracle? (It's got to be, right?)</p>
  
  <p>2) How do I need to restructure my query to pull this off? I'm guessing my WHERE EXISTS clause needs to go... but I'm still stuck as to where to place my JOIN. </p>