Re: [sqlite] Convert Access sql to SQLite sql

[Olaf Schmidt]

"Pavel Ivanov"  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  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  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

Re: [sqlite] Convert Access sql to SQLite sql

[Pavel Ivanov]

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  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

[sqlite] Convert Access sql to SQLite sql

[P.McFarlane]

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