BlitzNG
/
database.mod
mirror of https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: DELETE</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>DELETE</h1><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>
<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>


<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 <a href="lang_createtrigger.html">CREATE TRIGGER</a> 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>

<a name="trucateopt"></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 individual.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite version 3.6.5, the truncate optimization
also meant that the sqlite3_changes() and
sqlite3_total_changes() 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 version 3.6.5.

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

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


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