<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Query Language: DELETE</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
</script>
</div>
<div class=nosearch><h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>DELETE</h2></div><p><b><a href="syntax/delete-stmt.html">delete-stmt:</a></b>
<button id='x1067' onclick='hideorshow("x1067","x1068")'>hide</button></p>
 <div id='x1068' class='imgcontainer'>
 <img alt="syntax diagram delete-stmt" src="images/syntax/delete-stmt.gif" />
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x1069' onclick='hideorshow("x1069","x1070")'>show</button></p>
 <div id='x1070' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x1071' onclick='hideorshow("x1071","x1072")'>show</button></p>
 <div id='x1072' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</div>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x1073' onclick='hideorshow("x1073","x1074")'>show</button></p>
 <div id='x1074' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</div>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x1075' onclick='hideorshow("x1075","x1076")'>show</button></p>
 <div id='x1076' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram select-stmt" src="images/syntax/select-stmt.gif" />
<p><b><a href="syntax/common-table-expression.html">common-table-expression:</a></b>
<button id='x1077' onclick='hideorshow("x1077","x1078")'>show</button></p>
 <div id='x1078' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram common-table-expression" src="images/syntax/common-table-expression.gif" />
</div>
<p><b><a href="syntax/compound-operator.html">compound-operator:</a></b>
<button id='x1079' onclick='hideorshow("x1079","x1080")'>show</button></p>
 <div id='x1080' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram compound-operator" src="images/syntax/compound-operator.gif" />
</div>
<p><b><a href="syntax/join-clause.html">join-clause:</a></b>
<button id='x1081' onclick='hideorshow("x1081","x1082")'>show</button></p>
 <div id='x1082' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-clause" src="images/syntax/join-clause.gif" />
<p><b><a href="syntax/join-constraint.html">join-constraint:</a></b>
<button id='x1083' onclick='hideorshow("x1083","x1084")'>show</button></p>
 <div id='x1084' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-constraint" src="images/syntax/join-constraint.gif" />
</div>
<p><b><a href="syntax/join-operator.html">join-operator:</a></b>
<button id='x1085' onclick='hideorshow("x1085","x1086")'>show</button></p>
 <div id='x1086' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-operator" src="images/syntax/join-operator.gif" />
</div>
</div>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x1087' onclick='hideorshow("x1087","x1088")'>show</button></p>
 <div id='x1088' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</div>
<p><b><a href="syntax/result-column.html">result-column:</a></b>
<button id='x1089' onclick='hideorshow("x1089","x1090")'>show</button></p>
 <div id='x1090' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram result-column" src="images/syntax/result-column.gif" />
</div>
<p><b><a href="syntax/table-or-subquery.html">table-or-subquery:</a></b>
<button id='x1091' onclick='hideorshow("x1091","x1092")'>show</button></p>
 <div id='x1092' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</div>
</div>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x1093' onclick='hideorshow("x1093","x1094")'>show</button></p>
 <div id='x1094' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram type-name" src="images/syntax/type-name.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x1095' onclick='hideorshow("x1095","x1096")'>show</button></p>
 <div id='x1096' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/qualified-table-name.html">qualified-table-name:</a></b>
<button id='x1097' onclick='hideorshow("x1097","x1098")'>show</button></p>
 <div id='x1098' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram qualified-table-name" src="images/syntax/qualified-table-name.gif" />
</div>
<p><b><a href="syntax/with-clause.html">with-clause:</a></b>
<button id='x1099' onclick='hideorshow("x1099","x1100")'>show</button></p>
 <div id='x1100' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram with-clause" src="images/syntax/with-clause.gif" />
<p><b><a href="syntax/cte-table-name.html">cte-table-name:</a></b>
<button id='x1101' onclick='hideorshow("x1101","x1102")'>show</button></p>
 <div id='x1102' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram cte-table-name" src="images/syntax/cte-table-name.gif" />
</div>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x1103' onclick='hideorshow("x1103","x1104")'>show</button></p>
 <div id='x1104' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram select-stmt" src="images/syntax/select-stmt.gif" />
<p><b><a href="syntax/common-table-expression.html">common-table-expression:</a></b>
<button id='x1105' onclick='hideorshow("x1105","x1106")'>show</button></p>
 <div id='x1106' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram common-table-expression" src="images/syntax/common-table-expression.gif" />
</div>
<p><b><a href="syntax/compound-operator.html">compound-operator:</a></b>
<button id='x1107' onclick='hideorshow("x1107","x1108")'>show</button></p>
 <div id='x1108' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram compound-operator" src="images/syntax/compound-operator.gif" />
</div>
<p><b><a href="syntax/join-clause.html">join-clause:</a></b>
<button id='x1109' onclick='hideorshow("x1109","x1110")'>show</button></p>
 <div id='x1110' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-clause" src="images/syntax/join-clause.gif" />
<p><b><a href="syntax/join-constraint.html">join-constraint:</a></b>
<button id='x1111' onclick='hideorshow("x1111","x1112")'>show</button></p>
 <div id='x1112' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-constraint" src="images/syntax/join-constraint.gif" />
</div>
<p><b><a href="syntax/join-operator.html">join-operator:</a></b>
<button id='x1113' onclick='hideorshow("x1113","x1114")'>show</button></p>
 <div id='x1114' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-operator" src="images/syntax/join-operator.gif" />
</div>
</div>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x1115' onclick='hideorshow("x1115","x1116")'>show</button></p>
 <div id='x1116' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</div>
<p><b><a href="syntax/result-column.html">result-column:</a></b>
<button id='x1117' onclick='hideorshow("x1117","x1118")'>show</button></p>
 <div id='x1118' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram result-column" src="images/syntax/result-column.gif" />
</div>
<p><b><a href="syntax/table-or-subquery.html">table-or-subquery:</a></b>
<button id='x1119' onclick='hideorshow("x1119","x1120")'>show</button></p>
 <div id='x1120' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</div>
</div>
</div>
</div>


<p>The DELETE command removes records from the table identified by the
   <a href="syntax/qualified-table-name.html">qualified-table-name</a>.

<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
   WHERE clause <a href="lang_expr.html#booleanexpr">boolean expression</a> is true are deleted.
   Rows for which the expression is false or NULL are retained.

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

<p>The following restrictions apply to DELETE statements that occur within the
   body of a <a href="lang_createtrigger.html">CREATE TRIGGER</a> statement:

<ul>
  <li><p>The <span class='yyterm'>table-name</span> specified as part of a 
    DELETE statement within
    a trigger body must be unqualified.  In other words, the
    <i>schema-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 <a href="compile.html#enable_update_delete_limit">SQLITE_ENABLE_UPDATE_DELETE_LIMIT</a>
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:</p>

<p><b><a href="syntax/delete-stmt-limited.html">delete-stmt-limited:</a></b></p><div class='imgcontainer'>
 <img alt="syntax diagram delete-stmt-limited" src="images/syntax/delete-stmt-limited.gif"></img>
 </div>


<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 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 influenced by the ORDER BY clause.

<a name="truncateopt"></a>

<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
without having to visit each row of the table individually.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite <a href="releaselog/3_6_5.html">version 3.6.5</a> (2008-11-12), the truncate optimization
also meant that the <a href="c3ref/changes.html">sqlite3_changes()</a> and
<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces
and the <a href="pragma.html#pragma_count_changes">count_changes pragma</a>
will not actually return the number of deleted rows.  
That problem has been fixed as of <a href="releaselog/3_6_5.html">version 3.6.5</a> (2008-11-12).

<p>The truncate optimization can be permanently disabled for all queries
by recompiling
SQLite with the <a href="compile.html#omit_truncate_optimization">SQLITE_OMIT_TRUNCATE_OPTIMIZATION</a> compile-time switch.</p>

<p>The truncate optimization can also be disabled at runtime using
the <a href="c3ref/set_authorizer.html">sqlite3_set_authorizer()</a> interface.  If an authorizer callback
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
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>


