On 9/5/2012 9:53 PM, Yuriy Kaminskiy wrote:
Igor Tandetnik wrote:
On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can
UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE
beta.term =
alpha.term)
Will the database really be doing a select in beta for
every single line in alpha?
Yes - same as when implementing a join. How do you think a join is
performed - black magic?
Subquery: O(n*log(m)), join: O(n+m). Magic!
How does O(n+m) happen, exactly? A kind of tape merge algorithm, where
two lists are traversed in order in parallel? Last time I looked closely
at the output of EXPLAIN (which, I admit, was a few years ago), SQLite
never attempted anything of the sort. Does it now?
Note also that, when n is much smaller than m (note that the OP
mentioned 100K vs several million rows), O(n*log(m)) actually beats O(n+m).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
