The ftsdocs table contains an "idxed" field which is true if the document has been indexed and false if not.
When a search request occurs and pending documents are indexed for the first time, the ftsdocs table must be updated by setting the idxed column to true and also filling in several other columns with information pertinent to the search. The query is this: UPDATE ftsdocs SET idxed=1, name=NULL, (label,url,mtime) = (SELECT printf('Check-in [%%.16s] on %%s',blob.uuid, datetime(event.mtime)), printf('/timeline?
But the right-hand side (hereafter "RHS") must be a subquery expression.
Sql updating multiple columns one update statement
Two of the modified columns, "idxed" and "name", can be updated independently of the query.
But the three columns "label", "url", and "mtime" all require a join query against the "event" and "blob" tables.
Even in the JOIN form, the query can be made clearer through the use of row values: This later query generates exactly the same bytecode as the previous scalar formulation, but using syntax that it cleaner and easier to read.
The row-value notation is useful for updating two or more columns of a table from the result of a single query.
Initialize the scrolling window to the first 7 entries is easy: OFFSET gives the correct answer.
However, OFFSET requires time proportional to the offset value.
As new documents are added to the repository, they are not indexed right away.
Indexing is deferred until there is a search request.
Because the same query could be written without the use of row values, row values do not provide new capabilities.
However, many developers say that the row value format is easier to read, write, and debug.
Consider the following two UPDATE statements: Both UPDATE statements do exactly the same thing.