2
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.22 2005-01-09 05:57:45 tgl Exp $
3
PostgreSQL documentation
6
<refentry id="SQL-DELETE">
8
<refentrytitle id="SQL-DELETE-TITLE">DELETE</refentrytitle>
9
<refmiscinfo>SQL - Language Statements</refmiscinfo>
13
<refname>DELETE</refname>
14
<refpurpose>delete rows of a table</refpurpose>
17
<indexterm zone="sql-delete">
18
<primary>DELETE</primary>
23
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
28
<title>Description</title>
31
<command>DELETE</command> deletes rows that satisfy the
32
<literal>WHERE</literal> clause from the specified table. If the
33
<literal>WHERE</literal> clause is absent, the effect is to delete
34
all rows in the table. The result is a valid, but empty table.
39
<xref linkend="sql-truncate" endterm="sql-truncate-title"> is a
40
<productname>PostgreSQL</productname> extension that provides a
41
faster mechanism to remove all rows from a table.
46
By default, <command>DELETE</command> will delete rows in the
47
specified table and all its subtables. If you wish to delete only
48
from the specific table mentioned, you must use the
49
<literal>ONLY</literal> clause.
53
You must have the <literal>DELETE</literal> privilege on the table
54
to delete from it, as well as the <literal>SELECT</literal>
55
privilege for any table whose values are read in the <replaceable
56
class="parameter">condition</replaceable>.
61
<title>Parameters</title>
65
<term><replaceable class="parameter">table</replaceable></term>
68
The name (optionally schema-qualified) of an existing table.
74
<term><replaceable class="parameter">condition</replaceable></term>
77
A value expression that returns a value of type
78
<type>boolean</type> that determines the rows which are to be
87
<title>Outputs</title>
90
On successful completion, a <command>DELETE</> command returns a command
93
DELETE <replaceable class="parameter">count</replaceable>
95
The <replaceable class="parameter">count</replaceable> is the number
96
of rows deleted. If <replaceable class="parameter">count</replaceable> is
97
0, no rows matched the <replaceable
98
class="parameter">condition</replaceable> (this is not considered
107
<productname>PostgreSQL</productname> lets you reference columns of
108
other tables in the <literal>WHERE</> condition. For example, to
109
delete all films produced by a given producer, one might do
112
WHERE producer_id = producers.id AND producers.name = 'foo';
114
What is essentially happening here is a join between <structname>films</>
115
and <structname>producers</>, with all successfully joined
116
<structname>films</> rows being marked for deletion.
117
This syntax is not standard. A more standard way to do it is
120
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
122
In some cases the join style is easier to write or faster to
123
execute than the sub-select style. One objection to the join style
124
is that there is no explicit list of what tables are being used,
125
which makes the style somewhat error-prone; also it cannot handle
131
<title>Examples</title>
134
Delete all films but musicals:
136
DELETE FROM films WHERE kind <> 'Musical';
141
Clear the table <literal>films</literal>:
149
<title>Compatibility</title>
152
This command conforms to the SQL standard, except that the ability to
153
reference other tables in the <literal>WHERE</> clause is a
154
<productname>PostgreSQL</productname> extension.
159
<!-- Keep this comment at the end of the file
164
sgml-minimize-attributes:nil
165
sgml-always-quote-attributes:t
168
sgml-parent-document:nil
169
sgml-default-dtd-file:"../reference.ced"
170
sgml-exposed-tags:nil
171
sgml-local-catalogs:"/usr/lib/sgml/catalog"
172
sgml-local-ecat-files:nil