Have tested this and CTE pure SQL method is a lot faster (about 4 x) than my previous method in code:
strSQL = "SELECT ID, SD_BP FROM QR3PARAMS" RS1 = cConn.SQL1.ExecQuery(strSQL) strSQL = "UPDATE QR3PARAMS SET SD_BP = ? WHERE ID = ?" cConn.BeginTransaction Do While RS1.NextRow cConn.SQL1.ExecNonQuery2(strSQL, Array As String(Sqrt(RS1.GetDouble2(1)), RS1.GetInt2(0))) Loop cConn.EndTransaction This is B4A code on Android with the SQLCipher driver. RBS On Fri, Oct 18, 2019 at 8:26 PM Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 18.10.2019 um 19:45 schrieb Bart Smissaert: > > > Regarding: > > > > With r(s2, s, i) As (Select 2, 1, 1 Union All > > Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 > > Limit 32 > > ) Select s From r Order By i Desc Limit 1 > > > > How would this work if I wanted to update all the values in a table > column > > to have the square root? > > Well, as with any other (single-value-returning) Sub-Select > (which was enclosed in parentheses)... > > E.g. > "Update MyTable Set MyCol = (Select 1)" > would update MyCol with the value 1 across the whole table... > > Same thing basically (only "a bit larger") with a CTE-based > "Single-Value-SubSelect"... > > BTW, I've updated and tuned the thing a bit (which should now > offer more precision, and ~20% more performance as well): > > With r (x, y, i) As ( > Select ?, 1, 1 Union All > Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > > Note the question-marked "Parameter" for the "squared Input-Value" > (in the first Select Statement of the CTEs triple). > > Integrated into an Update-Query it could look this way: > > Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren... > With r (x, y, i) As ( > Select MySquaredValue, 1, 1 Union All > Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32 > ) Select y From r Order By i Desc Limit 1 > ) -- SubSelect-Closing-Paren... > > HTH > > Olaf > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
