Documentation Source Text

              {line INSERT OR ABORT}
              {line INSERT OR FAIL}
              {line INSERT OR IGNORE}
          }
          INTO
       }
       {line {optx /schema-name .} /table-name
             {opt AS /alias}
             {optx ( {loop /column-name ,} )}}
       {line {or
           {line VALUES {loop {line ( {loop expr ,} )} ,}}
           select-stmt
           {line DEFAULT VALUES}
         } {opt upsert-clause}}
  }
  upsert-clause {
    stack
      {line ON CONFLICT {opt ( {loop indexed-column ,} ) {opt WHERE expr} } DO } 
      {or
         NOTHING
         {line UPDATE SET 
              {loop {line {or /column-name column-name-list} = expr} ,}
              {optx WHERE expr}}
      }
  }
  pragma-stmt {
     line PRAGMA {optx /schema-name .} /pragma-name
          {or
              nil
              {line = pragma-value}
              {line ( pragma-value )}

            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT expr {optx {or OFFSET ,} expr}}
            }
        }
  }
  qualified-table-name {
     stack
       {line {optx /schema-name .} /table-name {opt AS /alias}}
       {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {opt /schema-name}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
}

set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {expr sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited upsert-clause}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(common-table-expression) {select-stmt {compound-select-stmt factored-select-stmt select-stmt simple-select-stmt}}
set syntax_linkage(compound-operator) {{} {factored-select-stmt select-stmt}}
set syntax_linkage(compound-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt}
set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
set syntax_linkage(cte-table-name) {{} {recursive-cte with-clause}}
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt indexed-column insert-stmt join-constraint ordering-term result-column select-core select-stmt simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}}
set syntax_linkage(insert-stmt) {{expr select-stmt upsert-clause with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt select-stmt simple-select-stmt update-stmt-limited}}
set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}

set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-07-00 (3.24.0)} {
<li> Add support for PostgreSQL-style [UPSERT].
<li> Enhance [ALTER TABLE] so that it recognizes "true" and "false" as
     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
}

chng {2018-04-10 (3.23.1)} {

    {{CREATE TABLE} createtable}
    {{CREATE VIRTUAL TABLE} createvtab}
    {{CREATE INDEX} createindex}
    {VACUUM vacuum}
    {{DROP TABLE} droptable}
    {{DROP INDEX} dropindex}
    {INSERT insert}
    {UPSERT upsert}
    {REPLACE replace}
    {DELETE delete}
    {UPDATE update}
    {SELECT select}
    {{WITH clause} withclause}
    {comment comment}
    {EXPLAIN explain}

row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<p>To avoid a parsing ambiguity, the SELECT statement should always
contain a WHERE clause, even if that clause is simply "WHERE true",
if the [upsert-clause] is present.  Without the WHERE clause, the
parser does not know if the token "ON" is part of a join constraint
on the SELECT, or the beginning of the [upsert-clause].

<li><p><b>INSERT INTO </b><i>table</i><b> DEFAULT VALUES;</b>
<p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.
The [upsert-clause] is not supported after DEFAULT VALUES.

</ol>

<p>
^The initial "INSERT" keyword can be replaced by
"REPLACE" or "INSERT OR <i>action</i>" to specify an alternative
constraint [ON CONFLICT|conflict resolution algorithm] to use during 

is supported for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

<p>The optional "AS <yyterm>alias</yyterm>" phrase provides an alternative
name for the table into which content is being inserted.  The alias name
can be used within WHERE and SET clauses of the [UPSERT].  If there is no
[upsert-clause], then the <yyterm>alias</yyterm> is pointless, but also
harmless.

<p>See the separate [UPSERT] documentation for the additional trailing
syntax that can cause an INSERT to behave as an UPDATE if the INSERT would
otherwise violate a uniqueness constraint.  The [upsert clause] is not
allowed on an "INSERT ... DEFAULT VALUES".


<tcl>
##############################################################################
Section upsert UPSERT {UPSERT {upsert clause}}

RecursiveBubbleDiagram upsert-clause
</tcl>

<p>UPSERT is a special syntax addition to [INSERT] that causes the
INSERT to behave as an [UPDATE] or a no-op if the INSERT would violate
a uniqueness constraint.
UPSERT is not standard SQL.  UPSERT in SQLite follows the
syntax established by PostgreSQL.
UPSERT syntax was added to SQLite with version 3.24.0 ([dateof:3.24.0]).

<p>An UPSERT is an ordinary [INSERT] statement that is followed by
the special ON CONFLICT clause shown above.

<p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords
is called the "conflict target".  The conflict target specifies a specific
uniqueness constraint that will trigger the upsert.  The conflict target
is required for DO UPDATE upserts, but is optional for DO NOTHING.
A DO NOTHING upsert without a conflict target works the same as an
[ON CONFLICT|INSERT OR IGNORE].

<p>If the insert operation would cause the uniqueness constraint identified
by the conflict-target clause to fail, then the insert is omitted and
either the DO NOTHING or DO UPDATE operation is performed instead.
In the case of a multi-row insert, this decision is made separately
for each row of the insert.

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

RecursiveBubbleDiagram conflict-clause
</tcl>

<p>The ON CONFLICT clause is a
non-standard extension specific to SQLite 
that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The ON CONFLICT clause described here has been a part of
SQLite since before version 3.0.0 ([dateof:3.0.0]).  The phrase
"ON&nbsp;CONFLICT" is also part of [UPSERT], which is an extension
to [INSERT] added in version 3.24.0 ([dateof:3.24.0]).  Do not
confuse these two separate uses of the "ON&nbsp;CONFLICT" phrase.

<p>^The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
the syntax reads more naturally.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>