StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMatching two big SQLITE3 tables
text
Body
copied!<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 <> 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 <> t2.f2</code> and <code>t1.f3 <> 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload