Thank you both for the discussion and conclusion. 2015-05-24 18:32 GMT+02:00 Simon Slavin <slavins at bigfraud.org>:
> > On 24 May 2015, at 4:58pm, Keith Medcalf <kmedcalf at dessus.com> wrote: > > > This is not possible and would make an update operation incredibly > slow. What you mean is that a row which is not updated is not updated. A > row which is updated, even if you are re-writing the same value, *IS* an > update operation. > > CREATE TABLE myTable (x INTEGER, orig INTEGER); > (imagine 10000 operations like this here): > INSERT INTO "myTable" VALUES(9636,9636); > INSERT INTO "myTable" VALUES(8655,8655); > INSERT INTO "myTable" VALUES(7966,7966); > INSERT INTO "myTable" VALUES(1647,1647); > INSERT INTO "myTable" VALUES(7780,7780); > INSERT INTO "myTable" VALUES(9088,9088); > > In the following, ignore the indented rows. Those are me putting the > original values back where they belong after each test. > > sqlite> UPDATE myTable SET x=1111 WHERE x<100; > Run Time: real 0.007 user 0.002405 sys 0.001365 > sqlite> UPDATE myTable SET x=orig; > Run Time: real 0.014 user 0.010427 sys 0.001255 > sqlite> UPDATE myTable SET x=orig WHERE x<100; > Run Time: real 0.008 user 0.002374 sys 0.001319 > sqlite> UPDATE myTable SET x=orig; > Run Time: real 0.016 user 0.010507 sys 0.001409 > sqlite> UPDATE myTable SET x=1111 WHERE x<9000; > Run Time: real 0.015 user 0.010315 sys 0.001305 > sqlite> UPDATE myTable SET x=orig; > Run Time: real 0.016 user 0.010493 sys 0.001333 > sqlite> UPDATE myTable SET x=orig WHERE x<9000; > Run Time: real 0.016 user 0.010673 sys 0.001282 > sqlite> UPDATE myTable SET x=orig; > Run Time: real 0.015 user 0.010674 sys 0.001383 > > I was wrong. You're right. The 'user' number is far larger when the > WHERE clause is selecting more rows. It is not larger when you're writing > back the same number as the number already in that column. So SQLite does > not compare the number you're writing with the number already in that > column and rewrite the row only if they're different. > > Therefore, to answer the OP, using the WHILE clause is more likely to mean > faster execution with less writing going on. Which is good all round. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >