As you might know, a float is only approximate, and trying to cast to REAL or another floating point type will not be an exact match, causing the where clause to evaluate false for the row. Fuerger, ensure no NULLs are being inserted in the price field for the same reason above. What
are you trying to do? Try using the ROUND() or CEIL() functions (or equivalent for SQLLite), or cast to an integral type like INT or BIGINT if that's what you want, to round to the nearest integraltype, and you will then be able to match all rows. Note that this kind of query with a function on a column often causes indexes not be used, reducingperformance and scalability. HTH. ________________________________ From: Corey Nelson <corey.nel...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Sunday, July 8, 2012 6:17 PM Subject: 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; 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 CAST (price AS REAL) >= 3; sqlite> SELECT * FROM GBP_CHF_BID WHERE CAST (price AS REAL) >= 3; 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> _______________________________________________ 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
