Re: [sqlite] DELETE only deletes some records, not others

DELETE only deletes some records, not others I tried CASTing price to REAL. Same result. Some get deleted, most don't. See below. Mini:01 corey$ sqlite3 02.fxdb SQLite version 3.7.7 2011-06-25 16:35:41 Enter ".help" for instructions Enter SQL statements terminated with a &qu

Re: [sqlite] DELETE only deletes some records, not others

Corey Nelson wrote: > Thanks Igor. Yup, it's corrupt. > > I had already suspected as much so I started writing a little program to > read everything and copy to a new database to save what I can. Is there a > better way to try and repair? One way is to use sqlite3

Re: [sqlite] DELETE only deletes some records, not others

Thank for the suggestions guys. I should have mentioned that price is TEXT. I used TEXT because the resulting database file was smaller. I've never had any problems with this until now (4 years). CREATE TABLE GBP_CHF_BID (date INTEGER PRIMARY KEY, price TEXT) I think the results below confirm

Re: [sqlite] DELETE only deletes some records, not others

I tried CASTing price to REAL. Same result. Some get deleted, most don't. See below. Mini:01 corey$ sqlite3 02.fxdb SQLite version 3.7.7 2011-06-25 16:35:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT * FROM GBP_CHF_BID WHERE CAST (price AS REAL) >= 3;

Re: [sqlite] DELETE only deletes some records, not others

Thanks Igor. Yup, it's corrupt. I had already suspected as much so I started writing a little program to read everything and copy to a new database to save what I can. Is there a better way to try and repair? Corey On Sun, Jul 8, 2012 at 5:34 PM, Igor Tandetnik wrote: >

Re: [sqlite] DELETE only deletes some records, not others

Corey Nelson wrote: > sqlite> DELETE FROM GBP_CHF_BID WHERE price LIKE "12.%"; > sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%"; > 129435210|12.0169 [more results snipped] I'd suspect a corrupted database. Run PRAGMA integrity_check; , see what it says. --

Re: [sqlite] DELETE only deletes some records, not others

Quoth Corey Nelson , on 2012-07-08 00:15:36 -0600: > sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%"; Aside from the more immediately relevant aspects the other posters already mentioned, remember that double quotation marks in SQL are normally used for

Re: [sqlite] DELETE only deletes some records, not others

On 8 Jul 2012, at 7:15am, Corey Nelson wrote: > sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%"; If price is a numeric field, then instead of the above, use > SELECT * FROM GBP_CHF_BID WHERE price >= 12 AND price < 13; Simon.

Re: [sqlite] DELETE only deletes some records, not others

I've never really used sqlite, but here's a lucky guess based on other databases I have used. Is 'price' column a Numeric field with decimal places? If so, I think that's your problem. For some reason when using LIKE on Select statement it automatically casts price to a String, and so the like

[sqlite] DELETE only deletes some records, not others

Some bad data found it's way into my database and I'm trying to delete it. But my DELETE command is only deleting some of the records while leaving most as is. See below. The sqlite database file is here . Am I doing something wrong?