sqlite> create table alpha (frequency, term); sqlite> create table beta (term, frequency); sqlite> create index betaterm on beta(term); sqlite> .explain sqlite> explain query plan update alpha set frequency = (select frequency from beta where beta.term >= alpha.term); sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE alpha (~1000000 rows) 0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 0 0 0 0 SEARCH TABLE beta USING INDEX betaterm (term>?) (~250000 rows) sqlite> explain update alpha set frequency = (select frequency from beta where beta.term >= alpha.term); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 38 0 00 2 Null 0 1 2 00 3 OpenRead 0 2 0 2 00 4 Rewind 0 8 0 00 5 Rowid 0 2 0 00 6 RowSetAdd 1 2 0 00 7 Next 0 5 0 01 8 Close 0 0 0 00 9 OpenWrite 0 2 0 2 00 10 RowSetRead 1 36 2 00 11 NotExists 0 10 2 00 12 Null 0 3 4 00 13 Null 0 5 0 00 14 Integer 1 6 0 00 15 OpenRead 1 3 0 2 00 16 OpenRead 2 4 0 keyinfo(1,BINARY) 00 17 Column 0 1 7 00 18 IsNull 7 28 0 00 19 SeekGe 2 28 7 1 00 20 Column 2 0 8 00 21 IsNull 8 27 0 00 22 IdxRowid 2 8 0 00 23 Seek 1 8 0 00 24 Column 1 1 9 00 25 Move 9 5 1 00 26 IfZero 6 28 -1 00 27 Next 2 20 0 00 28 Close 1 0 0 00 29 Close 2 0 0 00 30 SCopy 5 3 0 00 31 Column 0 1 4 00 32 NotExists 0 33 2 00 33 MakeRecord 3 2 8 bb 00 34 Insert 0 8 2 alpha 05 35 Goto 0 10 0 00 36 Close 0 0 0 00 37 Halt 0 0 0 00 38 Transaction 0 1 0 00 39 VerifyCookie 0 3 0 00 40 TableLock 0 2 1 alpha 00 41 TableLock 0 3 0 beta 00 42 Goto 0 2 0 00 sqlite>
sqlite> explain query plan select alpha.term, beta.frequency from alpha, beta where beta.term >= alpha.term; SELECT item[0] = {0:1} item[1] = {1:1} FROM {0,*} = alpha {1,*} = beta WHERE GE({1:0},{0:1}) END sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE alpha (~1000000 rows) 0 1 1 SEARCH TABLE beta USING INDEX betaterm (term>?) (~250000 rows) sqlite> explain select alpha.term, beta.frequency from alpha, beta where beta.term >= alpha.term; SELECT item[0] = {0:1} item[1] = {1:1} FROM {0,*} = alpha {1,*} = beta WHERE GE({1:0},{0:1}) END addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 22 0 00 2 OpenRead 0 2 0 2 00 3 OpenRead 1 3 0 2 00 4 OpenRead 2 4 0 keyinfo(1,BINARY) 00 5 Rewind 0 18 0 00 6 Column 0 1 1 00 7 IsNull 1 17 0 00 8 SeekGe 2 17 1 1 00 9 Column 2 0 2 00 10 IsNull 2 16 0 00 11 IdxRowid 2 2 0 00 12 Seek 1 2 0 00 13 Column 0 1 3 00 14 Column 1 1 4 00 15 ResultRow 3 2 0 00 16 Next 2 9 0 00 17 Next 0 6 0 01 18 Close 0 0 0 00 19 Close 1 0 0 00 20 Close 2 0 0 00 21 Halt 0 0 0 00 22 Transaction 0 0 0 00 23 VerifyCookie 0 3 0 00 24 TableLock 0 2 0 alpha 00 25 TableLock 0 3 0 beta 00 26 Goto 0 2 0 00 sqlite> exactly the same except the update copy's the values or sets null if the seek fails, whereas the join returns the join of all the matching beta rows in the join instead of just the first result. Of course, if you do not want to set alpha.frequency to null if there is no match, then you need: sqlite> explain update alpha set frequency = coalesce((select frequency from beta where beta.term >= alpha.term), alpha.frequency); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 40 0 00 2 Null 0 1 2 00 3 OpenRead 0 2 0 2 00 4 Rewind 0 8 0 00 5 Rowid 0 2 0 00 6 RowSetAdd 1 2 0 00 7 Next 0 5 0 01 8 Close 0 0 0 00 9 OpenWrite 0 2 0 2 00 10 RowSetRead 1 38 2 00 11 NotExists 0 10 2 00 12 Null 0 3 4 00 13 Null 0 5 0 00 14 Integer 1 6 0 00 15 OpenRead 1 3 0 2 00 16 OpenRead 2 4 0 keyinfo(1,BINARY) 00 17 Column 0 1 7 00 18 IsNull 7 28 0 00 19 SeekGe 2 28 7 1 00 20 Column 2 0 8 00 21 IsNull 8 27 0 00 22 IdxRowid 2 8 0 00 23 Seek 1 8 0 00 24 Column 1 1 9 00 25 Move 9 5 1 00 26 IfZero 6 28 -1 00 27 Next 2 20 0 00 28 Close 1 0 0 00 29 Close 2 0 0 00 30 SCopy 5 3 0 00 31 NotNull 3 33 0 00 32 Column 0 0 3 00 33 Column 0 1 4 00 34 NotExists 0 35 2 00 35 MakeRecord 3 2 8 bb 00 36 Insert 0 8 2 alpha 05 37 Goto 0 10 0 00 38 Close 0 0 0 00 39 Halt 0 0 0 00 40 Transaction 0 1 0 00 41 VerifyCookie 0 3 0 00 42 TableLock 0 2 1 alpha 00 43 TableLock 0 3 0 beta 00 44 Goto 0 2 0 00 With an index on beta.term it is O(n). Without an index it would be O(n*m). The straight join is O(n*m) with or without an index. I don't think there is any magic. It is simply the only way to implement the update expressed. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Yuriy Kaminskiy > Sent: Wednesday, 05 September, 2012 19:53 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] classic update join question > > 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! > Of course, query optimizer sometimes can rewrite subquery as join (or > opposite), > but I believe (unverified!) sqlite optimizer cannot do this currently. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users