1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: DELETE</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>DELETE</h2><h4><a href="syntaxdiagrams.html#delete-stmt">delete-stmt:</a></h4><blockquote> <img alt="syntax diagram delete-stmt" src="images/syntax/delete-stmt.gif"></img> </blockquote>
121
<h4><a href="syntaxdiagrams.html#qualified-table-name">qualified-table-name:</a></h4><blockquote> <img alt="syntax diagram qualified-table-name" src="images/syntax/qualified-table-name.gif"></img> </blockquote>
124
<p>The DELETE command removes records from the table identified by the
125
<i>qualified-table-name</i>.
127
<p>If the WHERE clause is not present, all records in the table are deleted.
128
If a WHERE clause is supplied, then only those rows for which the
129
result of evaluating the WHERE clause as a <a href="lang_expr.html#booleanexpr">boolean expression is true</a> are deleted.
131
<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>
133
<p>The following restrictions apply to DELETE statements that occur within the
134
body of a <a href="lang_createtrigger.html">CREATE TRIGGER</a> statement:
137
<li><p>The <i>table-name</i> specified as part of a DELETE statement within
138
a trigger body must be unqualified. In other words, the
139
<i>database-name</i><b>.</b> prefix on the table name is not allowed
140
within triggers. If the table to which the trigger is attached is
141
not in the temp database, then DELETE statements within the trigger
142
body must operate on tables within the same database as it. If the table
143
to which the trigger is attached is in the TEMP database, then the
144
unqualified name of the table being deleted is resolved in the same way as
145
it is for a top-level statement (by searching first the TEMP database, then
146
the main database, then any other databases in the order they were
149
<li><p>The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE
150
statements within triggers.</p>
152
<li><p>The LIMIT and ORDER BY clauses (described below) are unsupported for
153
DELETE statements within triggers.</p>
156
<h3>Optional LIMIT and ORDER BY clauses</h3>
158
<p>If SQLite is compiled with the <a href="compile.html#enable_update_delete_limit">SQLITE_ENABLE_UPDATE_DELETE_LIMIT</a>
159
compile-time option, then the syntax of the DELETE statement is
160
extended by the addition of optional ORDER BY and LIMIT clauses:</p>
162
<h4><a href="syntaxdiagrams.html#delete-stmt-limited">delete-stmt-limited:</a></h4><blockquote> <img alt="syntax diagram delete-stmt-limited" src="images/syntax/delete-stmt-limited.gif"></img> </blockquote>
165
<p>If a DELETE statement has a LIMIT clause, the maximum number of rows that
166
will be deleted is found by evaluating the accompanying expression and casting
167
it to an integer value. If the result of the evaluating the LIMIT clause
168
cannot be losslessly converted to an integer value, it is an error. A
169
negative LIMIT value is interpreted as "no limit". If the DELETE statement
170
also has an OFFSET clause, then it is similarly evaluated and cast to an
171
integer value. Again, it is an error if the value cannot be losslessly
172
converted to an integer. If there is no OFFSET clause, or the calculated
173
integer value is negative, the effective OFFSET value is zero.
175
<p>If the DELETE statement has an ORDER BY clause, then all rows that would
176
be deleted in the absence of the LIMIT clause are sorted according to the
177
ORDER BY. The first <i>M</i> rows, where <i>M</i> is the value found by
178
evaluating the OFFSET clause expression, are skipped, and the following
179
<i>N</i>, where <i>N</i> is the value of the LIMIT expression, are deleted.
180
If there are less than <i>N</i> rows remaining after taking the OFFSET clause
181
into account, or if the LIMIT clause evaluated to a negative value, then all
182
remaining rows are deleted.
184
<p>If the DELETE statement has no ORDER BY clause, then all rows that
185
would be deleted in the absence of the LIMIT clause are assembled in an
186
arbitrary order before applying the LIMIT and OFFSET clauses to determine
187
the subset that are actually deleted.
189
<p>The ORDER BY clause on a DELETE statement is used only to determine which
190
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
191
and is not influenced by the ORDER BY clause.
193
<a name="truncateopt"></a>
195
<h3>The Truncate Optimization</h3>
197
<p>When the WHERE is omitted from a DELETE statement and the table
198
being deleted has no triggers,
199
SQLite uses an optimization to erase the entire table content
200
without having to visit each row of the table individually.
201
This "truncate" optimization makes the delete run much faster.
202
Prior to SQLite <a href="releaselog/3_6_5.html">version 3.6.5</a>, the truncate optimization
203
also meant that the <a href="c3ref/changes.html">sqlite3_changes()</a> and
204
<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces
205
and the <a href="pragma.html#pragma_count_changes">count_changes pragma</a>
206
will not actually return the number of deleted rows.
207
That problem has been fixed as of <a href="releaselog/3_6_5.html">version 3.6.5</a>.
209
<p>The truncate optimization can be permanently disabled for all queries
211
SQLite with the <a href="compile.html#omit_truncate_optimization">SQLITE_OMIT_TRUNCATE_OPTIMIZATION</a> compile-time switch.</p>
213
<p>The truncate optimization can also be disabled at runtime using
214
the <a href="c3ref/set_authorizer.html">sqlite3_set_authorizer()</a> interface. If an authorizer callback
215
returns <a href="c3ref/c_deny.html">SQLITE_IGNORE</a> for an <a href="c3ref/c_alter_table.html">SQLITE_DELETE</a> action code, then
216
the DELETE operation will proceed but the truncate optimization will
217
be bypassed and rows will be deleted one by one.</p>
added
removed
lang_delete.html
