DELETE

Syntax

Single-table syntax:

Multiple-table syntax:

Or:

Trimming history:

Description

For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the function. TheWHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the clause is specified, the rows are deleted in the order that is specified. The clause places a limit on the number of rows that can be deleted.

For the multiple-table syntax, DELETE deletes from eachtbl_name the rows that satisfy the conditions. In this case, and > cannot be used. A DELETE can also reference tables which are located in different databases; see for the syntax.

where_condition is an expression that evaluates to true for each row to be deleted. It is specified as described in .

Currently, you cannot delete from a table and select from the same table in a subquery.

You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause. See .

As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table,TRUNCATE TABLE cannot be used whereas DELETE can. See , and .

AS

PARTITION

See for details.

FOR PORTION OF

See .

RETURNING

It is possible to return a resultset of the deleted rows for a single table to the client by using the syntax DELETE ... RETURNING select_expr [, select_expr2 ...]]

Any of SQL expression that can be calculated from a single row fields is allowed. Subqueries are allowed. The AS keyword is allowed, so it is possible to use aliases.

The use of aggregate functions is not allowed. RETURNING cannot be used in multi-table DELETEs.

Same Source and Target Table

It is possible to delete from a table with the same source and target. For example:

DELETE HISTORY

You can use DELETE HISTORY to delete historical information from .

Examples

How to use the and clauses:

How to use the RETURNING clause:

The following statement joins two tables: one is only used to satisfy a WHERE condition, but no row is deleted from it; rows from the other table are deleted, instead.

Deleting from the Same Source and Target

See Also

_{This page is licensed: GPLv2, originally from}