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
