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. Pavel On Tue, Dec 1, 2009 at 7:53 PM, P.McFarlane <phil_...@westnet.com.au> wrote: > I am currently converting a project that uses Access as a database to using > SQLite. Part of this involves rewriting sql queries. > So far this has been OK, however I have struck a problem with an UPDATE query. > The following access sql query updates some fields in a table depending on > existing values in other fields in the table and uses a sub query > > UPDATE EvAtemp INNER JOIN > [SELECT EstimateIndex.Date, QAK1Data.JobNo, CodesIndex.Code, > FunctionsIndex.Function, > EstimateDetail.Hours, EstimateDetail.Rate, EstimateDetail.EmpTime, > EstimateIndex.Contingent, > EstimateDetail.Hours*EstimateDetail.Rate AS Charge, > (EstimateDetail.Hours*EstimateDetail.Rate)*(1+(EstimateIndex.Contingent/100)) > AS TotalCharge > FROM (((EstimateDetail INNER JOIN EstimateIndex ON EstimateDetail.EstID = > EstimateIndex.EstID) > INNER JOIN QAK1Data ON EstimateIndex.EstProjNo = QAK1Data.ProjRecID) > INNER JOIN CodesIndex ON EstimateDetail.EstCodeRec = > CodesIndex.CodesIndexRecID) > INNER JOIN FunctionsIndex ON EstimateDetail.EstFuncRec = > FunctionsIndex.FunctionsIndexRecID]. AS Q ON EvAtemp.JobNo = Q.JobNo > SET EvAtemp.EstimateDate = [Q].[Date], EvAtemp.CodeEst = [Q].[Code], > EvAtemp.FunctionEst = [Q].[Function], > EvAtemp.HoursEst = [Q].[Hours], EvAtemp.RateEst = [Q].[Rate], > EvAtemp.EmpTimeE = [Q].[EmpTime], > EvAtemp.Contingent = [Q].[Contingent], EvAtemp.ChargeEst = [Q].[Charge], > EvAtemp.TotalCharge = [Q].[TotalCharge] > WHERE (((EvAtemp.Code)=[Q].[Code]) AND ((EvAtemp.Function)=[Q].[Function])); > > My attempt in SQLite is > > UPDATE EvAtemp > SET [EstDate] = [Q].[Date], > [CodeEst] = [Q].[Code], > [FunctionEst] = [Q].[Function], > [HoursEst] = [Q].[Hours], > [RateEst] = [Q].[Rate], > [EmpTimeE] = [Q].[EmpTime], > [Contingent] = [Q].[Contingent], > [ChargeEst] = [Q].[Charge], > [TotalCharge] = [Q].[TotalCharge ] > (SELECT [EstimateIndex].[Date], [QAK1Data].[JobNo], [CodesIndex].[Code], > [FunctionsIndex].[Function], [EstimateDetail].[Hours], > [EstimateDetail].[Rate], [EstimateDetail].[EmpTime], > [EstimateIndex].[Contingent], [EstimateDetail].[Hours] * > [EstimateDetail].[Rate] AS [Charge], ([EstimateDetail].[Hours] * > [EstimateDetail].[Rate]) * (1 + ([EstimateIndex].[Contingent] / > 100)) AS [TotalCharge] > FROM [EstimateIndex] INNER JOIN > [EstimateDetail] ON [EstimateIndex].[EstID] = [EstimateDetail].[EstID] > INNER JOIN > [QAK1Data] ON [EstimateIndex].[EstProjNo] = [QAK1Data].[ProjRecID] > INNER JOIN > [CodesIndex] ON [EstimateDetail].[EstCodeRec] = > [CodesIndex].[CodesIndexRecID] INNER JOIN > [FunctionsIndex] ON [EstimateDetail].[EstFuncRec] = > [FunctionsIndex].[FunctionsIndexRecID]) [Q] > WHERE [Code]=[Q].[Code] > AND [Function]=[Q].[Function] > AND [ProjNo] = [Q].[JobNo] > > This does not work. I have tried many variations with no success. > I hope that some one can point me in the right direction with an example of > correct useage. > > thanks > Phil > _______________________________________________ > 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
