title: SQL merging two big data tables without a unique key
body: <p>I have the following tables that need to be merged:</p>
  
  <p>table 1:</p>
  
  <blockquote>
    <p>main_key   |   sec_key  | old_number  | var1   | var2</p>
  </blockquote>
  
  <pre><code> 1      |  A    |    5     | AA | 11
   1      |  B    |    8     | BB | 22
  </code></pre>
  
  <p>table 2:</p>
  
  <blockquote>
    <p>main_key   | new_number  | var1   | var2</p>
  </blockquote>
  
  <pre><code> 1      |   2       | DD | 44
   1      |   3       | EE | 55
   1      |   7       | FF | 66
  </code></pre>
  
  <p>I cannot modify the tables (no inserts hence I cannot use 'merge', only UNION).</p>
  
  <p>Both tables contain a lot of data and I need the most efficient way to merge them.</p>
  
  <ul>
  <li>The merging should be according to the main_key and, for each of the table1.sec_key,</li>
  <li>if the table2.new_number &lt; table1.old_number, then update the values of table1.var1, table1.var2 to the corresponding values: table2.var1 and table2.var2.</li>
  </ul>
  
  <p>Also, there should be a line of the original table1 values with the old_number.</p>
  
  <p>Example:</p>
  
  <ul>
  <li>For the first table1.sec_key: its old_number is 5.</li>
  <li>table2.new_number '2' is smaller than 5, also table2.new_number '3' is smaller than 5 but table2.new_number '7' is bigger than 5</li>
  <li>so the output will include the old_numbers and the lines for new_number=2 and new_number=3:</li>
  </ul>
  
  <p>(The same goes for the rest of the sec_keys and for all other sec_keys in other main_keys.</p>
  
  <p>Expected output_table for the example:</p>
  
  <pre><code>main_key | sec_key | number | var1 | var2
      1    |    A    |    5   |  AA  |  11
      1    |    A    |    2   |  DD  |  44     
      1    |    A    |    3   |  EE  |  55
      1    |    B    |    8   |  BB  |  22  
      1    |    B    |    2   |  DD  |  44     
      1    |    B    |    3   |  EE  |  55
      1    |    B    |    7   |  FF  |  66
  </code></pre>
  
  <p>I thought of either using UNION or join, but not sure how to do this and keep the original line from table1 for each sec_key.</p>
  
  <p>My problem is that the main_key is not a unique key.
  I've also considered using CTE but not sure it's useful here.</p>