Hi Olaf, OK, thanks, that works fine indeed.
> Well, as with any other (single-value-returning) Sub-Select It is just that saw SQL's where the update part came after the CTE, eg: https://stackoverflow.com/questions/27637278/how-to-remove-duplicate-rows-with-cte-when-partitioning-by-another-tables-colum WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Created) AS Rnk FROM @Item AS i JOIN @ItemType AS it ON i.ItemTypeId = it.Id ) DELETE FROM cte WHERE Rnk > 1; 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
