On 2 Dec 2010, at 3:44pm, cricketfan wrote: > Hello I have a basic question and would be glad if someone can answer it. I > understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION" then > the transaction(s) within that block would be committed, number of > transactions could be 10,100,1000 or 10000..... . Otherwise, SQLITE by > default commits for every single insert,update,delete.
So far, very good. > I have a prepare statement(using limit clause so will get only 10 rows back) > followed by step(during which I use an update clause) so how would the > transaction concept behave in this case? It doesn't. You will get some results from SQLite by doing SELECT step to record 1 UPDATE record 1 step to record 2 UPDATE record 2 step to record 3 UPDATE record 3 ... but it means nothing in terms of SQL and precisely what it does may change between SQL engines and different versions of SQL engines. I might even argue that SQL engines should produce an error message when people try to do things like this. There are two possible solutions: A) The UPDATE command has a WHERE clause so put the SELECT criteria in that WHERE clause. This will enable you to roll both commands into one single UPDATE command. This is the simplest solution, and will execute the most quickly and efficiently. B) If that solution is not available because the calculations cannot be completed inside SQL, do the SELECT first, read the entire list of results into an array (list ?) in your chosen programming language, and only when the SELECT is finished, start looking through the results and doing UPDATE commands. You can, of course, issue a BEGIN TRANSACTION before the SELECT, and an END TRANSACTION after the last UPDATE. Both (A) and (B) are sound procedures and their results are clearly defined and will give the same results across all SQL engines and all versions of SQL engines (unless they're buggy !). > If I wrap the loop I use for > stepping with a begin transaction(followed by an end transaction when the > loop ends) would it be sufficient? Makes no difference. Doing an UPDATE inside your SELECT violates the rule no matter how you structure your transaction. A SELECT is a single operation and you can't do anything else until it is finished. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
