1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>DELETE</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-declare.html" title="DECLARE">
<link rel="next" href="sql-dropaggregate.html" title="DROP AGGREGATE">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-delete"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>DELETE — delete rows of a table</p>
</div>
<a name="id769601"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">DELETE FROM [ ONLY ] <em class="replaceable"><code>table</code></em>
[ USING <em class="replaceable"><code>usinglist</code></em> ]
[ WHERE <em class="replaceable"><code>condition</code></em> ]</pre>
</div>
<div class="refsect1" lang="en">
<a name="id769632"></a><h2>Description</h2>
<p> <code class="command">DELETE</code> deletes rows that satisfy the
<code class="literal">WHERE</code> clause from the specified table. If the
<code class="literal">WHERE</code> clause is absent, the effect is to delete
all rows in the table. The result is a valid, but empty table.
</p>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> <a href="sql-truncate.html">TRUNCATE</a> is a
<span class="productname">PostgreSQL</span> extension that provides a
faster mechanism to remove all rows from a table.
</p>
</div>
<p> By default, <code class="command">DELETE</code> will delete rows in the
specified table and all its child tables. If you wish to delete only
from the specific table mentioned, you must use the
<code class="literal">ONLY</code> clause.
</p>
<p> There are two ways to delete rows in a table using information
contained in other tables in the database: using sub-selects, or
specifying additional tables in the <code class="literal">USING</code> clause.
Which technique is more appropriate depends on the specific
circumstances.
</p>
<p> You must have the <code class="literal">DELETE</code> privilege on the table
to delete from it, as well as the <code class="literal">SELECT</code>
privilege for any table in the <code class="literal">USING</code> clause or
whose values are read in the <em class="replaceable"><code>condition</code></em>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id769735"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">ONLY</code></span></dt>
<dd><p> If specified, delete rows from the named table only. When not
specified, any tables inheriting from the named table are also processed.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>table</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of an existing table.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>usinglist</code></em></span></dt>
<dd><p> A list of table expressions, allowing columns from other tables
to appear in the <code class="literal">WHERE</code> condition. This is similar
to the list of tables that can be specified in the <a href="sql-select.html#sql-from"><code class="literal">FROM</code> Clause</a> of a
<code class="command">SELECT</code> statement; for example, an alias for
the table name can be specified. Do not repeat the target table
in the <em class="replaceable"><code>usinglist</code></em>,
unless you wish to set up a self-join.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt>
<dd><p> An expression returning a value of type
<code class="type">boolean</code>, which determines the rows that are to be
deleted.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="id769822"></a><h2>Outputs</h2>
<p> On successful completion, a <code class="command">DELETE</code> command returns a command
tag of the form
</p>
<pre class="screen">DELETE <em class="replaceable"><code>count</code></em></pre>
<p>
The <em class="replaceable"><code>count</code></em> is the number
of rows deleted. If <em class="replaceable"><code>count</code></em> is
0, no rows matched the <em class="replaceable"><code>condition</code></em> (this is not considered
an error).
</p>
</div>
<div class="refsect1" lang="en">
<a name="id769865"></a><h2>Notes</h2>
<p> <span class="productname">PostgreSQL</span> lets you reference columns of
other tables in the <code class="literal">WHERE</code> condition by specifying the
other tables in the <code class="literal">USING</code> clause. For example,
to delete all films produced by a given producer, one might do
</p>
<pre class="programlisting">DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';</pre>
<p>
What is essentially happening here is a join between <code class="structname">films</code>
and <code class="structname">producers</code>, with all successfully joined
<code class="structname">films</code> rows being marked for deletion.
This syntax is not standard. A more standard way to do it is
</p>
<pre class="programlisting">DELETE FROM films
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');</pre>
<p>
In some cases the join style is easier to write or faster to
execute than the sub-select style.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id769924"></a><h2>Examples</h2>
<p> Delete all films but musicals:
</p>
<pre class="programlisting">DELETE FROM films WHERE kind <> 'Musical';</pre>
<p>
</p>
<p> Clear the table <code class="literal">films</code>:
</p>
<pre class="programlisting">DELETE FROM films;</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id769952"></a><h2>Compatibility</h2>
<p> This command conforms to the SQL standard, except that the
<code class="literal">USING</code> clause and the ability to reference other tables
in the <code class="literal">WHERE</code> clause are <span class="productname">PostgreSQL</span>
extensions.
</p>
</div>
</div></body>
</html>
|
