Re: [sqlite] classic update join question

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

Re: [sqlite] classic update join question

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

Re: [sqlite] classic update join question

On 6 Sep 2012, at 2:53am, Yuriy Kaminskiy wrote: > Igor Tandetnik wrote: >> On 9/5/2012 12:38 PM, E. Timothy Uy wrote: >>> 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

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

Re: [sqlite] classic update join question

I like replying to myself, obviously ... Update alpha Set alpha.value = (select beta.value from beta where beta.key = alpha.key) Where exists (select * from beta where beta.key = alpha.key) Could be more efficiently: Update alpha Set alpha.value = coalesce((select beta.value from beta

Re: [sqlite] classic update join question

> Update alpha >Set alpha.value = beta.value > From alpha, beta > Where alpha.key = beta.key > > (which is how you would express an update of a join table in other DBMS's) > > Is equivalent to (and will be executed as) > > Update alpha >Set alpha.value = (select beta.value from beta

Re: [sqlite] classic update join question

I think what you are asking is whether an update through a cursor: Update alpha Set alpha.value = beta.value From alpha, beta Where alpha.key = beta.key (which is how you would express an update of a join table in other DBMS's) Is equivalent to (and will be executed as) Update alpha

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 5, 2012, at 4:57 PM, Richard Hipp wrote: > I think I'll stick with SQLite's magical processing of min()) "I think I'll stick with SQLite's magical processing of mine" -- there, I fixed it for you :P > the mtime value is a floating point number, and we all know the

Re: [sqlite] classic update join question

On Sep 5, 2012, at 11:00 PM, Igor Tandetnik wrote: >> And such statement should raise an exception if the scalar sub-query returns >> multiple rows, no? > > Definitely not in SQLite. I don't believe it would do that in other DBMS > either, but won't bet on it. SQLite

Re: [sqlite] classic update join question

On 9/5/2012 4:54 PM, Petite Abeille wrote: On Sep 5, 2012, at 10:38 PM, Igor Tandetnik wrote: A select statement that would most closely resemble your update statement would look like this: select frequency, (select frequency from beta where beta.term = alpha.term)

Re: [sqlite] classic update join question

On Sep 5, 2012, at 10:38 PM, Igor Tandetnik wrote: > A select statement that would most closely resemble your update statement > would look like this: > > select frequency, (select frequency from beta where beta.term = alpha.term) > from alpha; > > This statement will

Re: [sqlite] classic update join question

On 9/5/2012 4:35 PM, Igor Tandetnik wrote: On 9/5/2012 4:32 PM, E. Timothy Uy wrote: Igor, what happens if there are multiple hits for SELECT frequency FROM beta WHERE beta.term = alpha.term There can't be multiple hits, or even a single hit. This statement will not run, as it's not

Re: [sqlite] classic update join question

On 9/5/2012 4:32 PM, E. Timothy Uy wrote: Igor, what happens if there are multiple hits for SELECT frequency FROM beta WHERE beta.term = alpha.term There can't be multiple hits, or even a single hit. This statement will not run, as it's not syntactically valid. -- Igor Tandetnik

Re: [sqlite] classic update join question

Igor, what happens if there are multiple hits for SELECT frequency FROM beta WHERE beta.term = alpha.term (there aren't but, it is possible for there to be). Rob could be right in a sense. On Wed, Sep 5, 2012 at 1:28 PM, Igor Tandetnik wrote: > On 9/5/2012 4:20 PM, Rob

Re: [sqlite] classic update join question

On 9/5/2012 4:20 PM, Rob Richardson wrote: Well, I think you want a where clause on your main UPDATE query. What you wrote will set the frequency of every record in the alpha table to the value from the beta table, for every record in the beta table that matches an alpha record. (It's late,

Re: [sqlite] classic update join question

Well, I think you want a where clause on your main UPDATE query. What you wrote will set the frequency of every record in the alpha table to the value from the beta table, for every record in the beta table that matches an alpha record. (It's late, I'm tired and that's incoherent. I hope you

Re: [sqlite] classic update join question

On 9/5/2012 4:11 PM, E. Timothy Uy wrote: Are you saying that UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term = alpha.term) is just as efficient as it gets and equivalent to an update using join (in other dbs)? I don't know what other DBMS are doing. In SQLite, this

Re: [sqlite] classic update join question

Dear Igor, Are you saying that UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term = alpha.term) is just as efficient as it gets and equivalent to an update using join (in other dbs)? That would helpful to know. I do kind of imagine some kind of black magic... ;) On Wed,

Re: [sqlite] EXTERNAL: Re: sqlite3_trace() threadsafe

" It is not mentioned by the docs that your trace callback will be called encapsulated by a mutex - In other words: Are you sure that your trace callback is threadsafe ?" Thanks for the suggestion Marcus. I have tried encapsulating the callback with a mutex to make it threadsafe. However,

Re: [sqlite] sqlite3_trace() threadsafe

Am 2012-09-05 18:53, schrieb esum: Thank you so much for all help. I really appreciate it. /"All the same, I'm guessing that http://www.sqlite.org/src/info/39f763bfc0will fix your problem."/ I gave this new src code from the src tree a shot, but I seem to be getting the same behavior.

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Sep 5, 2012, at 3:03 PM, Richard Hipp wrote: >SELECT id, a, b, ..., min(mtime) >FROM tab >GROUP BY id >ORDER BY min(mtime) DESC; Perhaps something along these lines, i.e. a simple self-join: selectfoo.* from foo join ( select

Re: [sqlite] classic update join question

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 =

Re: [sqlite] (no subject)

On 09/05/2012 06:36 PM, Arbol One wrote: That would be the C++ Standard Template Library http://en.wikipedia.org/wiki/Standard_Template_Library I am learning the library and as a student I always have questions. The STL isn't necessarily a project, as it is defined in the C++ standard and

Re: [sqlite] sqlite3_trace() threadsafe

Thank you so much for all help. I really appreciate it. /"All the same, I'm guessing that http://www.sqlite.org/src/info/39f763bfc0will fix your problem."/ I gave this new src code from the src tree a shot, but I seem to be getting the same behavior. Interestingly though, I tried enforcing my

[sqlite] classic update join question

Hi sqlite-users, 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) or we can create a

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Am 05.09.2012 16:57, schrieb Richard Hipp: On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt wrote: Am 05.09.2012 15:58, schrieb Igor Tandetnik: Well, you could do something like this: SELECT id, a, b, ..., mtime FROM tab t1 where mtime = (select min(mtime) from tab t2 where

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Richard Hipp wrote: > In the actual use case that inspired this question (and for which, after > seeing the alternatives, I think I'll stick with SQLite's magical > processing of min()) the mtime value is a floating point number, and we all > know the hazards of comparing

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt wrote: > Am 05.09.2012 15:58, schrieb Igor Tandetnik: > > > Well, you could do something like this: >> >> SELECT id, a, b, ..., mtime FROM tab t1 >> where mtime = (select min(mtime) from tab t2 where t2.id=t1.id) >> ORDER BY mtime

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Am 05.09.2012 15:58, schrieb Igor Tandetnik: Well, you could do something like this: SELECT id, a, b, ..., mtime FROM tab t1 where mtime = (select min(mtime) from tab t2 where t2.id=t1.id) ORDER BY mtime DESC; Ah, nice ... this solves the problem of the potential "non-uniqueness" of mtime...

Re: [sqlite] (no subject)

Arbol One wrote: > Does anyone know if there is a mailing list for the STL project? Which of these do you mean by STL? http://en.wikipedia.org/wiki/Standard_Template_Library http://en.wikipedia.org/wiki/State_logic http://en.wikipedia.org/wiki/Subtitle_(captioning)

[sqlite] (no subject)

Does anyone know if there is a mailing list for the STL project? This e-mail is for the sole use of the intended recipient and may contain confidential or privileged information. Unauthorized use of its contents is prohibited. If you have received this e-mail in error, please notify sender

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Richard Hipp wrote: > Suppose you have a table like this: > >CREATE TABLE tab(ID, A, B, C, ..., MTIME); > > The ID entries are not unique. For each ID occurrence, the values for A, > B, C, and so forth might (or might not) be different. We do queries like > this: > >

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Temporary table? Then you will have rowids. On Wed, Sep 5, 2012 at 4:16 PM, Richard Hipp wrote: > On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein >wrote: > > > SELECT id, a, b, ..., mtime > > FROM tab > > WHERE rowid IN ( > > SELECT

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On 5 Sep 2012, at 2:03pm, Richard Hipp wrote: > *The problem to solve* is this: We want to restrict the output of the > query above to show only the entry with the smallest MTIME for each > distinct ID. > > As of SQLite version 3.7.11 >

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein wrote: > SELECT id, a, b, ..., mtime > FROM tab > WHERE rowid IN ( > SELECT id, min(mtime) > FROM tab > GROUP BY id > ) > ORDER BY mtime DESC; > In the actual application, "tab" is

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

Sorry, that won't work. On Wed, Sep 5, 2012 at 4:12 PM, Baruch Burstein wrote: > SELECT id, a, b, ..., mtime > FROM tab > WHERE rowid IN ( > SELECT id, min(mtime) > > FROM tab > GROUP BY id > ) > ORDER BY mtime DESC; > > > On Wed,

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

SELECT id, a, b, ..., mtime FROM tab WHERE rowid IN ( SELECT id, min(mtime) FROM tab GROUP BY id ) ORDER BY mtime DESC; On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp wrote: > > SELECT id, a, b, ..., min(mtime) > FROM tab >

[sqlite] Finding rows with MIN(MTIME) for all IDs

Suppose you have a table like this: CREATE TABLE tab(ID, A, B, C, ..., MTIME); The ID entries are not unique. For each ID occurrence, the values for A, B, C, and so forth might (or might not) be different. We do queries like this: SELECT id, a, b, ..., mtime FROM tab ORDER BY

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

sattu wrote: > select * from myTable LIMIT 100 OFFSET 0 //Execution Time is less than > 1sec > select * from myTable LIMIT 100 OFFSET 95000 //Execution Time is almost > 15secs http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

On 4 Sep 2012, at 3:56pm, sattu wrote: > What I observed is, if the offset is very high like say 9, then it takes > more time for the query to execute. Following is the time difference between > 2 queries with different offsets: > > > select * from myTable LIMIT

[sqlite] Sqlite Query Optimization (using Limit and Offset)

Following is the query that I use for getting a fixed number of records from a database with millions of records:- select * from myTable LIMIT 100 OFFSET 0 What I observed is, if the offset is very high like say 9, then it takes more time for the query to execute. Following is the time