StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have no experience with <code>SQLite</code>, but I gave your problem a try.</p> <p>As you already said, it is not possible to updated more than one table at once (the <a href="https://stackoverflow.com/questions/2067069/matching-two-big-sqlite3-tables/2067649#2067649">solution provided by Tristran</a> only works for <code>MySQL</code> as far as I know).</p> <p>First I update <code>t1</code> and set <code>sts=1</code> for every row (if only a few rows are affected, it might be more efficient to add a <code>WHERE</code>-clause to get the relevant rows), and <code>rowid_t2</code> to <code>t2.rowid</code> where all relevant columns match.</p> <pre><code>UPDATE t1 SET sts = 1, rowid_t2 = ( SELECT rowid FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 AND t2.f3 = t1.f3 ); </code></pre> <p>Then I do the same with <code>sts=2</code> and <code>sts=3</code> but only where <code>rowid_t2</code> has not already been set.</p> <pre><code>UPDATE t1 SET sts = 2, rowid_t2 = ( SELECT rowid FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 ) WHERE t1.rowid_t2 IS NULL; UPDATE t1 SET sts = 3, rowid_t2 = ( SELECT rowid FROM t2 WHERE t2.f1 = t1.f1 ) WHERE t1.rowid_t2 IS NULL; </code></pre> <p>Then I reset <code>t1.sts</code> that have been set to <code>3</code> but are not actually valid:</p> <pre><code>UPDATE t1 SET sts = NULL WHERE rowid_t2 IS NULL; </code></pre> <p>And finally I update <code>sts</code> in <code>t2</code> to the "lowest" method that matched in <code>t1</code>. So if a row in <code>t2</code> has one row in <code>t1</code> that matches for all criterias, and one that matches only for <code>f1</code>, I still set <code>sts=1</code>.</p> <pre><code>UPDATE t2 SET sts = ( SELECT MIN(sts) FROM t1 WHERE t1.rowid_t2 = t2.rowid ) </code></pre> <p>I have not tried with indexes, but I think you should have one for <code>t2.f1</code>, <code>t2.f2</code> and <code>t2.f3</code> for the first three updates (might need separate indexes with <code>SQLite</code>, not sure), and another one at <code>t1.rowid_t2</code> for the other two updates and for <code>t1.rowid_t2 IS NULL</code>.</p> <p>Try with some representative test data first, to see if the result is as expected and performance is ok.</p> <p>Good luck :)</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