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 matches some results. When doing it on Delete it doesn't cast it automatically, so I would try doing a cast on 'price' first and then trying to match that with LIKE.
If what I said makes no sense in Sqlite I'm sorry, but I couldn't try first for myself. I hope it helps, Hector Guilarte On Jul 8, 2012 8:44 PM, "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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
