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