<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.28 2006/09/16 00:30:18 momjian Exp $ PostgreSQL documentation --> <refentry id="SQL-DELETE"> <refmeta> <refentrytitle id="SQL-DELETE-TITLE">DELETE</refentrytitle> <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> <refnamediv> <refname>DELETE</refname> <refpurpose>delete rows of a table</refpurpose> </refnamediv> <indexterm zone="sql-delete"> <primary>DELETE</primary> </indexterm> <refsynopsisdiv> <synopsis> DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] [ USING <replaceable class="PARAMETER">usinglist</replaceable> ] [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ] </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <command>DELETE</command> deletes rows that satisfy the <literal>WHERE</literal> clause from the specified table. If the <literal>WHERE</literal> clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table. </para> <tip> <para> <xref linkend="sql-truncate" endterm="sql-truncate-title"> is a <productname>PostgreSQL</productname> extension that provides a faster mechanism to remove all rows from a table. </para> </tip> <para> By default, <command>DELETE</command> 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 <literal>ONLY</literal> clause. </para> <para> 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 <literal>USING</literal> clause. Which technique is more appropriate depends on the specific circumstances. </para> <para> The optional <literal>RETURNING</> clause causes <command>DELETE</> to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in <literal>USING</literal>, can be computed. The syntax of the <literal>RETURNING</> list is identical to that of the output list of <command>SELECT</>. </para> <para> You must have the <literal>DELETE</literal> privilege on the table to delete from it, as well as the <literal>SELECT</literal> privilege for any table in the <literal>USING</literal> clause or whose values are read in the <replaceable class="parameter">condition</replaceable>. </para> </refsect1> <refsect1> <title>Parameters</title> <variablelist> <varlistentry> <term><literal>ONLY</></term> <listitem> <para> If specified, delete rows from the named table only. When not specified, any tables inheriting from the named table are also processed. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="parameter">table</replaceable></term> <listitem> <para> The name (optionally schema-qualified) of an existing table. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="parameter">alias</replaceable></term> <listitem> <para> A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given <literal>DELETE FROM foo AS f</>, the remainder of the <command>DELETE</command> statement must refer to this table as <literal>f</> not <literal>foo</>. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="PARAMETER">usinglist</replaceable></term> <listitem> <para> A list of table expressions, allowing columns from other tables to appear in the <literal>WHERE</> condition. This is similar to the list of tables that can be specified in the <xref linkend="sql-from" endterm="sql-from-title"> of a <command>SELECT</command> statement; for example, an alias for the table name can be specified. Do not repeat the target table in the <replaceable class="PARAMETER">usinglist</replaceable>, unless you wish to set up a self-join. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="parameter">condition</replaceable></term> <listitem> <para> An expression returning a value of type <type>boolean</type>, which determines the rows that are to be deleted. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="PARAMETER">output_expression</replaceable></term> <listitem> <para> An expression to be computed and returned by the <command>DELETE</> command after each row is deleted. The expression may use any column names of the <replaceable class="PARAMETER">table</replaceable> or table(s) listed in <literal>USING</>. Write <literal>*</> to return all columns. </para> </listitem> </varlistentry> <varlistentry> <term><replaceable class="PARAMETER">output_name</replaceable></term> <listitem> <para> A name to use for a returned column. </para> </listitem> </varlistentry> </variablelist> </refsect1> <refsect1> <title>Outputs</title> <para> On successful completion, a <command>DELETE</> command returns a command tag of the form <screen> DELETE <replaceable class="parameter">count</replaceable> </screen> The <replaceable class="parameter">count</replaceable> is the number of rows deleted. If <replaceable class="parameter">count</replaceable> is 0, no rows matched the <replaceable class="parameter">condition</replaceable> (this is not considered an error). </para> <para> If the <command>DELETE</> command contains a <literal>RETURNING</> clause, the result will be similar to that of a <command>SELECT</> statement containing the columns and values defined in the <literal>RETURNING</> list, computed over the row(s) deleted by the command. </para> </refsect1> <refsect1> <title>Notes</title> <para> <productname>PostgreSQL</productname> lets you reference columns of other tables in the <literal>WHERE</> condition by specifying the other tables in the <literal>USING</literal> clause. For example, to delete all films produced by a given producer, one might do <programlisting> DELETE FROM films USING producers WHERE producer_id = producers.id AND producers.name = 'foo'; </programlisting> What is essentially happening here is a join between <structname>films</> and <structname>producers</>, with all successfully joined <structname>films</> rows being marked for deletion. This syntax is not standard. A more standard way to do it is <programlisting> DELETE FROM films WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo'); </programlisting> In some cases the join style is easier to write or faster to execute than the sub-select style. </para> </refsect1> <refsect1> <title>Examples</title> <para> Delete all films but musicals: <programlisting> DELETE FROM films WHERE kind <> 'Musical'; </programlisting> </para> <para> Clear the table <literal>films</literal>: <programlisting> DELETE FROM films; </programlisting> </para> <para> Delete completed tasks, returning full details of the deleted rows: <programlisting> DELETE FROM tasks WHERE status = 'DONE' RETURNING *; </programlisting> </para> </refsect1> <refsect1> <title>Compatibility</title> <para> This command conforms to the <acronym>SQL</acronym> standard, except that the <literal>USING</literal> and <literal>RETURNING</> clauses are <productname>PostgreSQL</productname> extensions. </para> </refsect1> </refentry>