[sqlite] Help on DELETE FROM...

Hi List, sorry for not being very sqlite specific here but I would like to have an advice on a delete operation for which I can't find the right sql command. Currently I do it on C programming level using a loop but I think there must be a better sql way. Anyway, here is the story: I have a

Re: [sqlite] Select statement not returning any result

On 17 Jan 2011, at 3:25am, Roger Binns wrote: > And just to blow your mind a little further, SQLite happily allows zero > length table and columns names. This works. > > CREATE TABLE ""("" ""); > INSERT INTO "" VALUES(3); > SELECT ""+"" FROM ""; Argh ! Simon.

[sqlite] JOIN bug in 3.7.2, not in 3.6.22

Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2 and 3.6.22 to compare the differences. The comments shows what alterations you can make to make the query return the expected result. :-David

Re: [sqlite] Help on DELETE FROM...

Hi, This is something which will work in round robin fashion. I will suggest something like a trigger which will delete the older entries, when the table is updated with new data. There are some papers out for implementing rrd from sql. Google them, they should be helpful VENKAT Bug

Re: [sqlite] Help on DELETE FROM...

Seem to me that putting it inside a trigger would be the best choice...then you don't have to worry about it unless you have speed concerns and only want to do this once a day or such. CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, DataID INTEGER); create trigger

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

This may be the patch that fixes your problem... http://www.sqlite.org/src/info/ece641eb89 Was released in 3.7.3 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

I just tried the 3.7.4 binary on Linux, and the bug is still around. :-David On 01/17/2011 04:25 PM, Black, Michael (IS) wrote: > This may be the patch that fixes your problem... > http://www.sqlite.org/src/info/ece641eb89 > > Was released in 3.7.3 > > Michael D. Black > Senior Scientist >

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

> -- if endtime is in a different position in the table, the query works > CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER); > CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT); > > INSERT INTO entry (id) VALUES ( 42); > > INSERT INTO interval (endtime,

Re: [sqlite] EXTERNAL:Re: JOIN bug in 3.7.2, not in 3.6.22

Hmmm...my initial testing on 3.7.4 worked...so here's how to reproduce. Analyze fixes it. Why does the first entry show 1,000,000 rows??? It appears the automatic index isn't working in this case Before analyze 0|0|0|SCAN TABLE entry USING INTEGER PRIMARY KEY (~100 rows) 0|1|1|SEARCH

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

On Mon, Jan 17, 2011 at 7:47 AM, David Burström wrote: > Hello all! > > I stumbled across this strange bug during Android development on 2.2.1 > late last night. Please run the following snippet in SQLite 3.7.2 and > 3.6.22 to compare the differences. The comments shows

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

Hmmm...my initial testing on 3.7.4 worked...so here's how to reproduce. Analyze fixes it. Why does the first entry show 1,000,000 rows??? It appears the automatic index isn't working in this case Before analyze 0|0|0|SCAN TABLE entry USING INTEGER PRIMARY KEY (~100 rows) 0|1|1|SEARCH

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

David Burström wrote: > SELECT starttime, endtime from entry LEFT JOIN interval ON > interval.entryid = entry.id GROUP BY entry.id HAVING starttime = > MAX(starttime); The behavior of this statement is unspecified. In standard SQL, it is syntactically invalid - in a

Re: [sqlite] Help on DELETE FROM...

Thanks Venkat and Michael, actually I'm doing this in fact only once per day during administration hours (i.e. in the night). Thanks for the trigger advice, I didn't yet consider a trigger for that purpose to keep the schema simple and reduce the (little) overhead involved when inserts are made

Re: [sqlite] Help on DELETE FROM...

Marcus Grimm wrote: > I have a table to record some history data, for example > items a user recently selected: > > CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, > DataID INTEGER); > > That table needs to trace only the last 10 events,

Re: [sqlite] Help on DELETE FROM...

On 17.01.2011 17:14, Igor Tandetnik wrote: > Marcus Grimm wrote: >> I have a table to record some history data, for example >> items a user recently selected: >> >> CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, >> DataID INTEGER); >> >>

Re: [sqlite] Help on DELETE FROM...

I came up with the logical opposite which I think does what you want select a.id from thi as a where a.id in (select thi.id from thi where a.userid=thi.userid order by timestamp limit 100 offset 10); You just need to set the limit value to some ridiculous number. Michael D. Black

Re: [sqlite] Help on DELETE FROM...

Marcus Grimm wrote: >> delete from THI where ID not in ( >> select ID from THI t2 where t2.UserID = UserId >> order by t2.TimeStamp desc limit 10); A correction: I think the subselect should say "select t2.ID from ..." > Thanks Igor! > but a stupid question:

Re: [sqlite] Help on DELETE FROM...

The "NOT IN" approach doesn't work..here's sample data using select rather than delete to show the result. sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, DataID INTEGER); INSERT INTO "THI" VALUES(0,10,10,0);

Re: [sqlite] Help on DELETE FROM...

On 17.01.2011 17:26, Black, Michael (IS) wrote: > I came up with the logical opposite which I think does what you want > > select a.id from thi as a where a.id in (select thi.id from thi where > a.userid=thi.userid order by timestamp limit 100 offset 10); > > You just need to set the limit

Re: [sqlite] Help on DELETE FROM...

Black, Michael (IS) wrote: > The "NOT IN" approach doesn't work..here's sample data using select rather > than delete to show the result. > > sqlite> select * from THI where ID not in ( >...> select ID from THI t2 where t2.UserID = UserId >...> order by

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

Dan, Richard, Igor, thanks for your input, and yes, it seems as if the gamble is no longer safe. Hopefully I'm the only one that has run into this side effect ;) :-David On 01/17/2011 04:57 PM, Igor Tandetnik wrote: > David Burström wrote: >> SELECT starttime,

Re: [sqlite] Help on DELETE FROM...

Igor Tandetnik wrote: > delete from THI where ID not in ( >select ID from THI t2 where t2.UserID = UserId >order by t2.TimeStamp desc limit 10); Correction: delete from THI where ID not in ( select t2.ID from THI t2 where t2.UserID = THI.UserId order by

Re: [sqlite] Help on DELETE FROM...

Thank you very much Michael and Igor for taking your time! I'm still not sure if Igor's last version will work but I'll not start to argue with Igor about Sql! :-) Nor do I know how to put Michaels solution in a DELETE statement. I have to leave for today, but I'll come back to this after I

Re: [sqlite] EXTERNAL:Re: Help on DELETE FROM...

Competing solutions...gotta' love it...I prefer the postive solution as it's a list of what WILL be deleted. Though there's some merit in "tell me what will be left". delete from THI where ID in ( select t2.ID from THI t2 where t2.UserID=UserID order by t2.TimeStamp desc limit 100 offset

Re: [sqlite] Help on DELETE FROM...

Competing solutions...gotta' love it...I prefer the postive solution as it's a list of what WILL be deleted. Though there's some merit in "tell me what will be left". delete from THI where ID in ( select t2.ID from THI t2 where t2.UserID=UserID order by t2.TimeStamp desc limit 100 offset

Re: [sqlite] Committing to a savepointL

On Thursday, January 13, 2011 7:55:28 pm Pavel Ivanov wrote: > What you really want is for database engine to allow to have two > parallel writing transactions and for it to not lock the whole > database in those transactions but do some fine-grained locking > instead. Well, that would work, but

[sqlite] creating unique indexes, good or bad?

I am adding some indexes to an existing database to improve performance. I am 99.9% sure they are unique, but... it was a while ago that I was in that code. Are there any performance reasons to make them unique or make them not unique? From the stand point of risk, my inclination is to make

Re: [sqlite] Propose minor incompatible API change

On Tuesday, January 11, 2011 1:54 AM, Richard Hipp wrote: > So the question to you, gentle reader, is should we make this change, and > break backwards compatibility, albeit in a very obscure way, or should we > be > hard-nosed and force hundreds or perhaps thousands of smartphone > application