It's probably not a good idea, because it depends on some behaviour that is not specified, but I once used a trick like this to get good performance:
CREATE TABLE abc(a, b); UPDATE abc SET b = user2(a, b) WHERE <condition> AND user1(a, b);
SQLite loops through the rows where <condition> is true, and remembers those for which user1() returns true. It then runs a second loop through those rows and calls user2() for each of the remembered rows, setting 'b' to the return value.
But like I said, it depends on unspecified behaviour so you had better have some assert() statements to make sure SQLite is calling the functions in the expected order.
Two comments:
This approach should handle many cases which might seem to need UPDATEs nested within a SELECT. Thank you for reminding me how versatile SQL (as implemented by SQLite) is.
It looks like good SQL, totally within specs (as abridged below), to my too-often clueless eyes. Can you elaborate where your concerns about unspecified behavio(u)r are? I suspect that, as usual, I am missing something obvious, but maybe you are being overly cautious.
UPDATE statement from the web page (abridged--CONFLICT clause and db name omitted for readability)
-----------------------------------------------------------------
UPDATE table-name SET assignment [, assignment]* [WHERE expr]
assignment ::= column-name = expr
The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifies a column name to the left of the equals sign and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. A WHERE clause can be used to restrict which rows are updated.
------------------------------------------------------------------
TIA,
Gerry, big SQLite fan
-- ------------------ Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
