Documentation Source Text

Check-in [0bc1f4cfc7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add an example of using row values in an UPDATE to the row value document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0bc1f4cfc747b660c97b2b1e627ac9b7ee77a3b0
User & Date: drh 2016-09-23 14:33:41
Context
2016-09-26
13:16
Update the change log to show the 2% performance gain over 3.14.2. check-in: bf03335759 user: drh tags: trunk
2016-09-23
14:33
Add an example of using row values in an UPDATE to the row value document. check-in: 0bc1f4cfc7 user: drh tags: trunk
11:32
Fix a LHS/RHS mixup in the rowvalue documentation. check-in: db4e5cec36 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/rowvalue.in.

213
214
215
216
217
218
219


















































220
221
222
223
224
225
226
 WHERE (t1.prodid,t1.qty) = (t2.prodid,t2.qty)
   AND t2.ordid=365;
</codeblock>

<p>This later query generates exactly the same [bytecode] as the previous
scalar formulation, but using syntax that it cleaner and
easier to read.



















































<h2>Clarity of presentation</h2>

<p>Sometimes the use of row values just makes the SQL easier to read
and write.  Consider the following two UPDATE statements:

<codeblock>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 WHERE (t1.prodid,t1.qty) = (t2.prodid,t2.qty)
   AND t2.ordid=365;
</codeblock>

<p>This later query generates exactly the same [bytecode] as the previous
scalar formulation, but using syntax that it cleaner and
easier to read.

<h2>Update multiple columns of a table based on a query</h2>

<p>The row-value notation is useful for updating two or more columns
of a table from the result of a single query.
An example of this is in the full-text search feature of the
[https://www.fossil-scm.org/|Fossil version control system].

<p>In the Fossil full-text search system,
documents that participate in the full-text search (wiki pages, tickets,
check-ins, documentation files, etc) are tracked by a table called
"ftsdocs" (<u>f</u>ull <u>t</u>ext <u>s</u>earch <u>doc</u>ument<u>s</u>).
As new documents are added to the repository, they are not indexed right
away.  Indexing is deferred until there is a search request.  The
ftsdocs table contains an "idxed" field which is true if the document
has been indexed and false if not.

<p>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.  That other information is obtained from a join.  The
query is this:

<codeblock>
UPDATE ftsdocs SET
  idxed=1,
  name=NULL,
  (label,url,mtime) = 
      (SELECT printf('Check-in &#91;%%.16s&#93; on %%s',blob.uuid,
                     datetime(event.mtime)),
              printf('/timeline?y=ci&amp;c=%%.20s',blob.uuid),
              event.mtime
         FROM event, blob
        WHERE event.objid=ftsdocs.rid
          AND blob.rid=ftsdocs.rid)
WHERE ftsdocs.type='c' AND NOT ftsdocs.idxed
</codeblock>

<p>(See the 
[https://www.fossil-scm.org/fossil/artifact/e5d6a82d?ln=1594-1605|source code]
for further detail.  Other examples
[https://www.fossil-scm.org/fossil/artifact/e5d6a82d?ln=1618-1628|here] and
[https://www.fossil-scm.org/fossil/artifact/e5d6a82d?ln=1641-1650|here].)

<p>Five out of nine columns in the ftsdocs table are updated.  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.  Without row values,
the equivalent UPDATE would require that the join be repeated three times, 
once for each column to be updated.

<h2>Clarity of presentation</h2>

<p>Sometimes the use of row values just makes the SQL easier to read
and write.  Consider the following two UPDATE statements:

<codeblock>