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 price is indeed TEXT. Comparing the price to 3 doesn't work because 12 is alphabetically less than 3. The part that really baffles me is that some records get deleted while most don't. It makes me think I that the file is corrupted somehow. (Or just maybe I found a bug in sqlite3.) Any other suggestions? sqlite> SELECT * FROM GBP_CHF_BID WHERE price >= 3; sqlite> SELECT * FROM GBP_CHF_BID WHERE price >= 12; 1294352100000|12.0169 1294352102000|12.017 1294352107000|12.0166 1294352125000|12.0168 1294352129000|12.0166 1294352139000|12.0168 1294352150000|12.017 1294352160000|12.0161 1294352174000|12.0169 1294352182000|12.0171 1294352212000|12.0151 1294352217000|12.0182 1294352220000|12.0177 1294352226000|12.0156 1294352228000|12.0157 1294352229000|12.0177 1294352230000|12.0178 1294352232000|12.0177 1294352233000|12.0178 1294352234000|12.0176 1294352236000|12.0177 1294352238000|12.0178 1294352252000|12.0179 1294352257000|12.0147 1294352258000|12.0175 1294352259000|12.0178 1294352261000|12.0147 1294352264000|12.0148 1294352265000|12.0147 1294352280000|12.0135 1294352281000|12.0138 1294352282000|12.0133 1294352284000|12.0149 1294352292000|12.014 1294352312000|12.0125 1294352314000|12.0146 1294352319000|12.0144 1294352320000|12.0136 1294352323000|12.0138 1294352337000|12.014 1294352342000|12.0124 1294352360000|12.0138 1294352373000|12.0124 1294352379000|12.0105 1294352385000|12.0124 1294352387000|12.0121 1294352388000|12.0117 1294352415000|12.0118 1294352440000|12.0125 1294352446000|12.0105 1294352459000|12.011 1294352466000|12.0117 1294352469000|12.011 sqlite> DELETE FROM GBP_CHF_BID WHERE price >= 3; sqlite> DELETE FROM GBP_CHF_BID WHERE price >= 12; sqlite> SELECT * FROM GBP_CHF_BID WHERE price >= 3; sqlite> SELECT * FROM GBP_CHF_BID WHERE price >= 12; 1294352100000|12.0169 1294352102000|12.017 1294352107000|12.0166 1294352125000|12.0168 1294352129000|12.0166 1294352139000|12.0168 1294352150000|12.017 1294352160000|12.0161 1294352174000|12.0169 1294352182000|12.0171 1294352212000|12.0151 1294352217000|12.0182 1294352220000|12.0177 1294352226000|12.0156 1294352228000|12.0157 1294352229000|12.0177 1294352230000|12.0178 1294352232000|12.0177 1294352233000|12.0178 1294352234000|12.0176 1294352236000|12.0177 1294352238000|12.0178 1294352252000|12.0179 1294352257000|12.0147 1294352258000|12.0175 1294352259000|12.0178 1294352261000|12.0147 1294352264000|12.0148 1294352265000|12.0147 1294352280000|12.0135 1294352281000|12.0138 1294352282000|12.0133 1294352284000|12.0149 1294352292000|12.014 sqlite> DELETE FROM GBP_CHF_BID WHERE price LIKE '12.%'; sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE '12.%'; 1294352100000|12.0169 1294352102000|12.017 1294352107000|12.0166 1294352125000|12.0168 1294352129000|12.0166 1294352139000|12.0168 1294352150000|12.017 1294352160000|12.0161 1294352174000|12.0169 1294352182000|12.0171 1294352212000|12.0151 1294352217000|12.0182 1294352220000|12.0177 1294352226000|12.0156 1294352228000|12.0157 1294352229000|12.0177 1294352230000|12.0178 1294352232000|12.0177 1294352233000|12.0178 1294352234000|12.0176 1294352236000|12.0177 1294352238000|12.0178 1294352252000|12.0179 1294352257000|12.0147 1294352258000|12.0175 1294352259000|12.0178 1294352261000|12.0147 1294352264000|12.0148 1294352265000|12.0147 1294352280000|12.0135 1294352281000|12.0138 1294352282000|12.0133 1294352284000|12.0149 1294352292000|12.014 sqlite> Corey On Sun, Jul 8, 2012 at 12:15 AM, Corey Nelson <corey.nel...@gmail.com>wrote: > 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<https://docs.google.com/open?id=0B567nEzakPDFbktGbUVGeHVLTGM> > . > > Am I doing something wrong? Is my database file corrupted some how? > > 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 price LIKE "12.%"; > 1294352100000|12.0169 > 1294352102000|12.017 > 1294352107000|12.0166 > 1294352125000|12.0168 > 1294352129000|12.0166 > 1294352139000|12.0168 > 1294352150000|12.017 > 1294352160000|12.0161 > 1294352174000|12.0169 > 1294352182000|12.0171 > 1294352212000|12.0151 > 1294352217000|12.0182 > 1294352220000|12.0177 > 1294352226000|12.0156 > 1294352228000|12.0157 > 1294352229000|12.0177 > 1294352230000|12.0178 > 1294352232000|12.0177 > 1294352233000|12.0178 > 1294352234000|12.0176 > 1294352236000|12.0177 > 1294352238000|12.0178 > 1294352252000|12.0179 > 1294352257000|12.0147 > 1294352258000|12.0175 > 1294352259000|12.0178 > 1294352261000|12.0147 > 1294352264000|12.0148 > 1294352265000|12.0147 > 1294352280000|12.0135 > 1294352281000|12.0138 > 1294352282000|12.0133 > 1294352284000|12.0149 > 1294352292000|12.014 > 1294352312000|12.0125 > 1294352314000|12.0146 > 1294352319000|12.0144 > 1294352320000|12.0136 > 1294352323000|12.0138 > 1294352337000|12.014 > 1294352342000|12.0124 > 1294352360000|12.0138 > 1294352373000|12.0124 > 1294352379000|12.0105 > 1294352385000|12.0124 > 1294352387000|12.0121 > 1294352388000|12.0117 > 1294352415000|12.0118 > 1294352440000|12.0125 > 1294352446000|12.0105 > 1294352459000|12.011 > 1294352466000|12.0117 > 1294352469000|12.011 > sqlite> DELETE FROM GBP_CHF_BID WHERE price LIKE "12.%"; > sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%"; > 1294352100000|12.0169 > 1294352102000|12.017 > 1294352107000|12.0166 > 1294352125000|12.0168 > 1294352129000|12.0166 > 1294352139000|12.0168 > 1294352150000|12.017 > 1294352160000|12.0161 > 1294352174000|12.0169 > 1294352182000|12.0171 > 1294352212000|12.0151 > 1294352217000|12.0182 > 1294352220000|12.0177 > 1294352226000|12.0156 > 1294352228000|12.0157 > 1294352229000|12.0177 > 1294352230000|12.0178 > 1294352232000|12.0177 > 1294352233000|12.0178 > 1294352234000|12.0176 > 1294352236000|12.0177 > 1294352238000|12.0178 > 1294352252000|12.0179 > 1294352257000|12.0147 > 1294352258000|12.0175 > 1294352259000|12.0178 > 1294352261000|12.0147 > 1294352264000|12.0148 > 1294352265000|12.0147 > 1294352280000|12.0135 > 1294352281000|12.0138 > 1294352282000|12.0133 > 1294352284000|12.0149 > 1294352292000|12.014 > sqlite> > > > Corey > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
