Documentation Source Text

Check-in [32506c4e82]
Login

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

Overview
Comment:Changes to lang_update.html.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 32506c4e8282fb93bafe1461e695a958a16100da
User & Date: dan 2010-09-23 18:46:48.000
Context
2010-09-24
07:59
Add a definition for interpreting the result of an expression as a boolean value (e.g. for WHERE or HAVING clauses) to lang_expr.html. Link to this from lang_update.html, lang_delete.html and lang_select.html instead of repeating the definition on each page. (check-in: 36c1765653 user: dan tags: trunk)
2010-09-23
18:46
Changes to lang_update.html. (check-in: 32506c4e82 user: dan tags: trunk)
2010-09-21
16:58
Further changes to lang_update.html. (check-in: e7259b8c39 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054


1055
1056
1057
1058
1059



1060
1061
1062
1063

1064




1065
1066
1067
1068

1069





































1070
1071
1072
1073
1074
1075
1076
##############################################################################
Section DELETE delete {DELETE *DELETEs}

BubbleDiagram delete-stmt 1
BubbleDiagram qualified-table-name
</tcl>

<p>^The DELETE command is used to remove records from a table.
^The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>^Without a WHERE clause, all rows of the table are removed.
^If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>



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

<p>^There are additional syntax restrictions on DELETE statements that
occur within the body of a [CREATE TRIGGER] statement.  ^The <i>table-name</i>



must be unqualified. 
^(In other words, the <i>database-name</i><b>.</b> prefix
on the table name is not allowed within triggers. )^
^The table from which to delete must be in the same

database as the table to which the trigger is attached.</p>





<p>^The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE
statements within triggers.</p>


<p>^The LIMIT clause (described below) is unsupported within triggers.</p>






































<tcl>hd_fragment truncateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>^When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content







|
<
|
<

|
|
<
>
>



|
|
>
>
>
|
|
|
|
>
|
>
>
>
>
|
|
|

>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1040
1041
1042
1043
1044
1045
1046
1047

1048

1049
1050
1051

1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
##############################################################################
Section DELETE delete {DELETE *DELETEs}

BubbleDiagram delete-stmt 1
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>
  <li><p>^The <i>table-name</i> specified as part of a DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>database-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as
    it is for a top-level statement (by searching first the TEMP database, then
    the main database, then any other databases in the order they were
    attached).
    
  <li><p>^The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE
    statements within triggers.</p>

  <li><p>^The LIMIT and ORDER BY clauses (described below) are unsupported for
    DELETE statements within triggers.</p>
</ul>

<h3>Optional LIMIT and ORDER BY clauses</h3>

<p>^(If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:)^</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>

<p>^If a DELETE statement has a LIMIT clause, the maximum number of rows that
will be deleted is found by evaluating the accompanying expression and casting
it to an integer value. ^If the result of the evaluating the LIMIT clause
cannot be losslessly converted to an integer value, it is an error. ^A 
negative LIMIT value is interpreted as "no limit". ^(If the DELETE statement 
also has an OFFSET clause, then it is similarly evaluated and cast to an
integer value. Again, it is an error if the value cannot be losslessly
converted to an integer.)^ ^If there is no OFFSET clause, or the calculated
integer value is negative, the effective OFFSET value is zero.

<p>^(If the DELETE statement has an ORDER BY clause, then all rows that would 
be deleted in the absence of the LIMIT clause are sorted according to the 
ORDER BY. The first <i>M</i> rows, where <i>M</i> is the value found by
evaluating the OFFSET clause expression, are skipped, and the following 
<i>N</i>, where <i>N</i> is the value of the LIMIT expression, are deleted.)^
^If there are less than <i>N</i> rows remaining after taking the OFFSET clause
into account, or if the LIMIT clause evaluated to a negative value, then all
remaining rows are deleted.

<p>^If the DELETE statement has no ORDER BY clause, then all rows that
would be deleted in the absence of the LIMIT clause are assembled in an
arbitrary order before applying the LIMIT and OFFSET clauses to determine 
the subset that are actually deleted.

<p>^(The ORDER BY clause on an DELETE statement is used only to determine which
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
and is not influenced by the ORDER BY clause.)^

<tcl>hd_fragment truncateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>^When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120

<p>The truncate optimization can also be disabled at runtime using
the [sqlite3_set_authorizer()] interface.  ^If an authorizer callback
returns [SQLITE_IGNORE] for an [SQLITE_DELETE] action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

<h3>Use Of LIMIT</h3>

<p>^(If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:)^</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>

<p>^The optional LIMIT clause can be used to limit the number of
rows deleted, and thereby limit the size of the transaction.
^The ORDER BY clause on a DELETE statement
is used only to determine which rows fall
within the LIMIT.  ^The order in which rows are deleted is arbitrary
and is not determined by the ORDER BY clause.</p>

<p>^The presence of a LIMIT clause defeats the truncate optimization
causing all rows being deleted to be visited.</p>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach *DETACH

BubbleDiagram detach-stmt 1
</tcl>








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







1134
1135
1136
1137
1138
1139
1140


















1141
1142
1143
1144
1145
1146
1147

<p>The truncate optimization can also be disabled at runtime using
the [sqlite3_set_authorizer()] interface.  ^If an authorizer callback
returns [SQLITE_IGNORE] for an [SQLITE_DELETE] action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>



















<tcl>
###############################################################################
Section {DETACH DATABASE} detach *DETACH

BubbleDiagram detach-stmt 1
</tcl>