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 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