title: Matching two big SQLITE3 tables
body: <p>I have two tables:</p>
  
  <p><strong>t1:</strong> <code>f1, f2, f3, f4, rowid_t2, sts</code></p>
  
  <p><strong>t2:</strong> <code>f1, f2, f3, f4, sts</code></p>
  
  <p>with different amounts of records that exceeds 10 millions.</p>
  
  <p>I need to match them using <code>f1</code>, <code>f2</code> and <code>f3</code> of each table as the keys, the relation is<br> that one record of t1 can match with one record in t2 or many records of t1 can match with one record in t2, the matching depends on the conditions indicated in the three operations below.</p>
  
  <hr>
  
  <p>I need to perform the next matching operations:</p>
  
  <p><strong>a)</strong> If <code>t1.f1 = t2.f1</code> and <code>t1.f2 = t2.f2</code> and <code>t1.f3 = t2.f3</code> then I must update the rowid of <code>t2</code> into <code>t1.rowid_t2</code> and save <code>t1.sts=1</code>, <code>t2.sts=1</code> in the records matched.</p>
  
  <p><strong>b)</strong> If <code>t1.f1 = t2.f1</code> and <code>t1.f2 = t2.f2</code> and <code>t1.f3 &lt;&gt; t2.f3</code> then I must update the rowid of <code>t2</code> into <code>rowid_t2</code> and save <code>t1.sts=2</code>, <code>t2.sts=2</code> in the records matched.</p>
  
  <p><strong>c)</strong> If <code>t1.f1 = t2.f1</code> and <code>t1.f2 &lt;&gt; t2.f2</code> and <code>t1.f3 &lt;&gt; t2.f3</code> then I must update the rowid of <code>T2</code> into rowid_t2 and save <code>t1.sts=3</code>, <code>t2.sts=3</code> in the records matched.</p>
  
  <hr>
  
  <p>I have 2 questions:</p>
  
  <ol>
  <li><p>Can I solve the problem using <code>UPDATE</code>? If yes, it would be nice if you can show me the solution only for a)</p></li>
  <li><p>How many indexes should I create to optimize the necessary UPDATEs and SELECTs for the three operations?</p></li>
  </ol>
  
  <p>Many thanks!!</p>