Documentation Source Text

BubbleDiagram qualified-table-name
</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^(If a WHERE clause is supplied, then only those rows for which evaluating
   the WHERE clause and casting the result to a NUMERIC value produces a 
   result other than NULL or zero (integer value 0 or real value 0.0).)^

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>
................................................................................
</table>

<p>^Note that the result from casting any non-BLOB value into a 
BLOB and the result from casting any BLOB value into a non-BLOB value
may be different depending on whether the database [encoding] is UTF-8,
UTF-16be, or UTF-16le.


























<h3>Functions</h3>
<p>^Both [corefunc|simple] and [aggfunc|aggregate] functions are supported.
(For presentation purposes, simple functions are further subdivided into
[corefunc | core functions] and [datefunc | date-time functions].)
^A simple function can be used in any expression.  ^Simple functions return
a result immediately based on their inputs.  ^Aggregate functions
may only be used in a SELECT statement.  ^Aggregate functions compute
................................................................................
"INNER JOIN", "JOIN" and "," operators)^, but is 
<a href=optoverview.html#manctrl>handled slightly differently by the query
optimizer</a>. Otherwise, it is the cartesian product modified 
according to one or more of the following bullet points: 

<ul>
  <li> <p>^(If there is an ON clause specified, then the ON expression is
       evaluated for each row of the cartesian product and the result cast to 
       a numeric value as if by a [CAST expression]. All rows for which the
       expression evaluates to NULL or zero (integer value 0 or real value 0.0)
       are excluded from the dataset.)^

  <li> <p>^If there is a USING clause specified as part of the join-constraint,
       then each of the column names specified must exist in the datasets to 
       both the left and right of the join-op. ^(For each pair of namesake
       columns, the expression "lhs.X = rhs.X" is evaluated for each row of
       the cartesian product and the result cast to a numeric value. All rows
       for which one or more of the expressions evaluates to NULL or zero are
       excluded from the result set.)^ ^When comparing values as a result of a
       USING clause, the normal rules for handling affinities, collation
       sequences and NULL values in comparisons apply. ^The column from the
       dataset on the left-hand side of the join operator is considered to
       be on the left-hand side of the comparison operator (=) for the purposes
       of collation sequence and affinity precedence.

       <p>^For each pair of columns identified by a USING clause, the column
       from the right-hand dataset is omitted from the joined dataset. ^This 
       is the only difference between a USING clause and its equivalent ON
       constraint.

  <li> <p>^(If the NATURAL keyword is added to any of the join-ops, then an
................................................................................
       

<p><b>2. WHERE clause filtering.</b>
<tcl>hd_fragment whereclause</tcl>
<tcl>hd_keywords {WHERE clause}</tcl>

<p>^(If a WHERE clause is specified, the WHERE expression is evaluated for 
each row in the input data and the result cast to a numeric value. All rows
for which the WHERE clause expression evaluates to a NULL value or to zero
(integer value 0 or real value 0.0) are excluded from the dataset before
continuing.)^

<p><b>3. Generation of the set of result rows.</b>
<tcl>hd_fragment resultset</tcl>
<tcl>hd_keywords {result-set expressions} {GROUP BY}</tcl>

<p>Once the input data from the FROM clause has been filtered by the
................................................................................
    values are considered equal. ^The usual rules for [collation|selecting a
    collation sequence] with which to compare text values apply when evaluating
    expressions in a GROUP BY clause.  ^The expressions in the GROUP BY clause
    do <em>not</em> have to be expressions that appear in the result. ^The
    expressions in a GROUP BY clause may not be aggregate expressions.

    <p>^(If a HAVING clause is specified, it is evaluated once for each group 
    of rows and cast to an integer value. If the result of evaluating the
    HAVING clause is NULL or zero (integer value 0), the group is discarded.)^
    ^If the HAVING clause is an aggregate expression, it is evaluated across
    all rows in the group. ^If a HAVING clause is a non-aggregate expression,
    it is evaluated with respect to an arbitrarily selected row from the group. 
    ^The HAVING expression may refer to values, even aggregate functions, that
    are not in the result.</p>

    <p>^Each expression in the result-set is then evaluated once for each
    group of rows. ^If the expression is an aggregate expression, it is 
    evaluated across all rows in the group. ^Otherwise, it is evaluated against
    a single arbitrarily chosen row from within the group. ^If there is more
    than one non-aggregate expression in the result-set, then all such
    expressions are evaluated for the same row.
................................................................................
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^(Otherwise, the UPDATE affects only those
rows for which evaluating the WHERE clause expression and 
[CAST expression|casting the result] to a NUMERIC value produces a value other
than NULL or zero (integer value 0 or real value 0.0).)^ ^It is not an error if
the WHERE clause does not evaluate to a non-NULL, non-zero value for any row in
the table - this just means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of

BubbleDiagram qualified-table-name
</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
   result of evaluating the WHERE clause as a [boolean expression|
   boolean expression is true] are deleted.

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>
................................................................................
</table>

<p>^Note that the result from casting any non-BLOB value into a 
BLOB and the result from casting any BLOB value into a non-BLOB value
may be different depending on whether the database [encoding] is UTF-8,
UTF-16be, or UTF-16le.


<tcl>hd_fragment booleanexpr {boolean expression}</tcl>
<h3>Boolean Expressions</h3>

<p>The SQL language features several contexts where an expression is 
evaluated and the result converted to a boolean (true or false) value. These
contexts are:

  <ul>
    <li> the WHERE clause of a SELECT, UPDATE or DELETE statement,
    <li> the ON or USING clause of a join in a SELECT statement,
    <li> the HAVING clause of a SELECT statement,
    <li> the WHEN clause of an SQL trigger, and
    <li> the WHEN clause or clauses of some CASE expressions.
  </ul>

<p>^(To convert the results of an SQL expression to a boolean value, SQLite
first casts the result to a NUMERIC value in the same way as a 
[CAST expression]. A NULL or zero value (integer value 0 or real value 0.0) is
considered to be false. All other values are considered true.)^

<p>^(For example, the values NULL, 0.0, 0, 'english' and '0' are all considered
to be false.)^ ^(Values 1, 1.0, 0.1, -0.1 and '1english' are considered to 
be true.)^

<h3>Functions</h3>
<p>^Both [corefunc|simple] and [aggfunc|aggregate] functions are supported.
(For presentation purposes, simple functions are further subdivided into
[corefunc | core functions] and [datefunc | date-time functions].)
^A simple function can be used in any expression.  ^Simple functions return
a result immediately based on their inputs.  ^Aggregate functions
may only be used in a SELECT statement.  ^Aggregate functions compute
................................................................................
"INNER JOIN", "JOIN" and "," operators)^, but is 
<a href=optoverview.html#manctrl>handled slightly differently by the query
optimizer</a>. Otherwise, it is the cartesian product modified 
according to one or more of the following bullet points: 

<ul>
  <li> <p>^(If there is an ON clause specified, then the ON expression is
       evaluated for each row of the cartesian product as a 
       [boolean expression]. All rows for which the expression evaluates to 

       false are excluded from the dataset.)^

  <li> <p>^If there is a USING clause specified as part of the join-constraint,
       then each of the column names specified must exist in the datasets to 
       both the left and right of the join-op. ^(For each pair of namesake
       columns, the expression "lhs.X = rhs.X" is evaluated for each row of
       the cartesian product as a [boolean expression]. All rows for which one
       or more of the expressions evaluates to false are excluded from the
       result set.)^ ^When comparing values as a result of a USING clause, the
       normal rules for handling affinities, collation sequences and NULL
       values in comparisons apply. ^The column from the dataset on the
       left-hand side of the join operator is considered to be on the left-hand
       side of the comparison operator (=) for the purposes of collation 
       sequence and affinity precedence.

       <p>^For each pair of columns identified by a USING clause, the column
       from the right-hand dataset is omitted from the joined dataset. ^This 
       is the only difference between a USING clause and its equivalent ON
       constraint.

  <li> <p>^(If the NATURAL keyword is added to any of the join-ops, then an
................................................................................
       

<p><b>2. WHERE clause filtering.</b>
<tcl>hd_fragment whereclause</tcl>
<tcl>hd_keywords {WHERE clause}</tcl>

<p>^(If a WHERE clause is specified, the WHERE expression is evaluated for 
each row in the input data as a [boolean expression]. All rows for which the
WHERE clause expression evaluates to false are excluded from the dataset before

continuing.)^

<p><b>3. Generation of the set of result rows.</b>
<tcl>hd_fragment resultset</tcl>
<tcl>hd_keywords {result-set expressions} {GROUP BY}</tcl>

<p>Once the input data from the FROM clause has been filtered by the
................................................................................
    values are considered equal. ^The usual rules for [collation|selecting a
    collation sequence] with which to compare text values apply when evaluating
    expressions in a GROUP BY clause.  ^The expressions in the GROUP BY clause
    do <em>not</em> have to be expressions that appear in the result. ^The
    expressions in a GROUP BY clause may not be aggregate expressions.

    <p>^(If a HAVING clause is specified, it is evaluated once for each group 
    of rows as a [boolean expression]. If the result of evaluating the
    HAVING clause is false, the group is discarded.)^ ^If the HAVING clause is
    an aggregate expression, it is evaluated across all rows in the group. ^If
    a HAVING clause is a non-aggregate expression, it is evaluated with respect
    to an arbitrarily selected row from the group.  ^The HAVING expression may
    refer to values, even aggregate functions, that are not in the result.</p>


    <p>^Each expression in the result-set is then evaluated once for each
    group of rows. ^If the expression is an aggregate expression, it is 
    evaluated across all rows in the group. ^Otherwise, it is evaluated against
    a single arbitrarily chosen row from within the group. ^If there is more
    than one non-aggregate expression in the result-set, then all such
    expressions are evaluated for the same row.
................................................................................
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those
rows for which the result of evaluating the WHERE clause expression as a 
[boolean expression|boolean expression is true]. ^It is not an error if the

WHERE clause does not evaluate to true for any row in the table - this just
means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of