Documentation Source Text

</pre></blockquote>

<p>In this last example, the phonebook2 entry is only
updated if the validDate for the newly inserted value is
newer than the entry already in the table.  If the table already
contains an entry with the same name and a current validDate,
then the WHERE clause causes the DO UPDATE to become a no-op.

<tcl>hd_fragment parseambig {UPSERT parsing ambiguity}</tcl>
<h3>Parsing Ambiguity</h3>

<p>When the [INSERT] statement to which the UPSERT is attached
takes its values from a [SELECT] statement, there is a potential
parsing ambiguity.  The parser might not be able to tell if the
"ON" keyword is introducing the UPSERT or if it is the ON clause
of a join.  To work around this, the SELECT statement should always
include a WHERE clause, even if that WHERE clause is just
"WHERE true".

<p>Ambiguious use of ON:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<p>Ambiguity resolved using a WHERE clause:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2 <font color="blue">WHERE true</font>
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<h3>Limitations</h3>

<p>UPSERT does not currently work for [virtual tables].


<tcl>

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.

<p>Window functions come in two varieties: 
[aggregate window functions] and
[built-in window functions].  Every aggregate window function
can also work as a ordinary aggregate function, simply by omitting
the OVER and FILTER clauses.  Furthermore, all of the built-in
[aggfunc|aggregate functions] of SQLite can be used as an
aggregate window function by adding an appropriate OVER clause.
Applications can register new aggregate window functions using
the [sqlite3_create_window_function()] interface.
The built-in window functions, however, require special-case
handling in the query planner and hence new window functions
that exhibit the exceptional properties found in the built-in
window functions cannot be added by the application.

<p>Here is an example using the built-in row_number()
window function:

<codeblock>
  CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');