title: SQL update with sub-query takes too long to run
body: <p>I have a SQL update query that runs with my test data, but doesn't complete (2 hours or more) with my production data.</p>
  
  <p><strong>The purpose of the query</strong></p>
  
  <p>I have an ADDRESSES table that uses code strings instead of IDs. So for example ADDRESSES.COUNTRY_CODE = "USA" instead of 3152. For referential integrity, I am changing these code strings to code IDs.</p>
  
  <p><strong>Schema</strong></p>
  
  <p>ADDRESSES (~356,000 records)</p>
  
  <ul>
  <li>ADDR_ID (PK)</li>
  <li>COUNTRY_CODE (varchar)</li>
  <li>Address line 1 (varchar)</li>
  <li>etc.</li>
  </ul>
  
  <p>COUNTRY_CODES</p>
  
  <ul>
  <li>CODE_ID (PK)</li>
  <li>CODE_STRING (varchar)</li>
  <li>etc. </li>
  </ul>
  
  <p><strong>Steps</strong></p>
  
  <p>First, I create a temporary table to store the address records with the appropriate code ID:</p>
  
  <pre><code>CREATE TABLE ADDRESS_TEMP
  AS
     SELECT ADDR_ID, CODE_ID
       FROM    ADDRESSES
            LEFT JOIN
               COUNTRY_CODES
            ON ADDRESSES.COUNTRY_CODE = CODE_STRING
  </code></pre>
  
  <p>Second, I null the COUNTRY_CODE column and change its type to NUMBER.</p>
  
  <p>Third I set the COUNTRY_CODE column to the code IDs:</p>
  
  <pre><code>UPDATE ADDRESSES
     SET COUNTRY_CODE =
            (SELECT ADDRESS_TEMP.CODE_ID
               FROM ADDRESS_TEMP
              WHERE ADDRESS_TEMP.ADDR_ID = ADDRESSES.ADDR_ID)
  </code></pre>
  
  <p>It is this third step that is taking hours to complete (2 hours and counting). The ADDRESSES table has ~356,000 records. There is no error; it is still running.</p>
  
  <p><strong>Question</strong></p>
  
  <p>Why isn't this update query completing? Is it dramatically inefficient? I think I can see how the sub-query might be an N<sup>2</sup> algorithm, but I'm  inexperienced with SQL.</p>