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
