SQLite Forum

Getting rid of double DATE()
That circumvents executing the date function call twice by executing it four times.

Perhaps you mean something like this:

  WITH now(now)
    as (
        select date('now', 'localtime')
       cte(now, idx)
    AS (
        SELECT now,
               ifnull(max(lastUsedIdx), 0) + 1
          FROM selectRandom, now
         WHERE lastUsed == now
UPDATE selectRandom
   SET (lastUsed, lastUsedIdx) = (SELECT now, idx FROM cte)
 WHERE description = :teaToBrew;

Note that "typing" does not necessarily equate to "executing", which is why there is the EXPLAIN QUERY PLAN (.eqp on) and EXPLAIN (.eqp full) in the CLI.

Of course, the repeated execution of the date function "takes the place of" the generation and use of a materialized view so avoiding the executions of such a non-expensive function call probably will not provide much (if any) actual benefit.  The above is, however, the CTE equivalent of using UPDATE ... FROM ...