"Pavel Ivanov" <paiva...@gmail.com> schrieb im Newsbeitrag news:f3d9d2130912020440r1777d1ado6d9938755f80...@mail.gmail.com...
> Your Access query doesn't have good equivalent in SQLite. Your options are: > 1. Execute SELECT separately and then for each row in the result issue > an UPDATE with necessary values (UPDATE can be prepared and you can > just bind all values). > > 2. Insert results of SELECT into some temporary table and then issue > one UPDATE statement like this: > > UPDATE EvAtemp SET > EvAtemp.EstimateDate = (select Date from TempTable tt where tt.Code = > EvAtemp.Code and tt.Function = EvAtemp.Function), > EvAtemp.CodeEst = (select Code from TempTable tt where tt.Code = > EvAtemp.Code and tt.Function = EvAtemp.Function), > ... > > This will work much-much slower than first approach. > > 3. Modify your SELECT so that it joins with EvAtemp too to get from > there rowid of the row which should be updated, insert results into > temporary table and issue UPDATE like this: > > UPDATE EvAtemp SET > EvAtemp.EstimateDate = (select Date from TempTable tt where > tt.EvAtempROWID = EvAtemp.rowid), > EvAtemp.CodeEst = (select Code from TempTable tt where tt. > EvAtempROWID = EvAtemp.rowid), > ... > > This will work faster than 2nd approach but I believe it's still > slower than the 1st. Yep, good hints - and when googling, one can find also messages which suggest an: 4. Insert Or Replace Into ... Select ... approach. But doing so would (although working) involve a Delete/Recreate instead of an Update of records in many cases. What I fiddled out in the meantime is something, which maybe could be included into the core possibly, since it involves "stuff which is already there" in SQLite. A working Demo-Source is posted here (using the VB-language): http://groups.google.de/group/microsoft.public.vb.database/msg/20bc947f3d5bdea4 In short again... 5. Alternative to an Update Tbl Set <list> From (Select ...) As Qry Where Cond - define a (temporary) View for all columns of [Tbl], which are involed in the Update-Stmt above (the ones defined in the Set-list, as well as the ones, used within the final "Update-Where-Condition" - define a second (temporary) View for the SubSelect [Qry], so that we are able later on, to rearrange the Column-Order in a way, that it matches with the order of the Columns in the first created view above (to meet the "Set-Pairs" and eventual compair-pairs of the Where-Condition). - define an Instead Of Trigger for the first View... assuming the first view was named [v_Tbl] having columns A,B,C: Create Temp Trigger tr_upd_Tbl Instead Of Insert On v_Tbl Begin Update Tbl Set A = new.A, B = new.B Where C = new.C; End; ...note, that the new.xxx-names in the expressions can (should) match the lefthandside naming directly, which eases programmatic construction of that trigger-definition. - finally the "triggering": Insert Into v_Tbl Select <ordered-fieldlist> From v_Qry ...if we take care in the needed fieldlist-Def for the SubSelect- View [v_Qry], that the "Set-, and expression-pair righthandsides" match with the Column-Order, defined for [v_Tbl], then we can execute the whole thing - and the performance is not bad, already tested that... - oh, and dont forget the "cleanup-drops" for the temporary views and the temporary trigger. Don't know, if one does work "out of specification", if an Update is performed inside an "Instead Of Insert" trigger, but the above steps perform really good + it can be "formalized" nicely, to construct the statements also programmatically (maybe at some "PreProcessor-level", at least in the wrappers, if something like that is not planned to integrate into the core-engine). Olaf Schmidt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
