7.5.10 DELETE

Table of Contents Previous Next



7.5.10 DELETE
Use the DELETE statement to delete one or more rows from a table. The syntax for the ECPGPlus DELETE statement is the same as the syntax for the SQL statement, but you can use parameter markers and host variables any place that an expression is allowed. The syntax is:
[FOR exec_count] DELETE FROM [ONLY] table [[AS] alias]
[USING using_list]
[WHERE condition | WHERE CURRENT OF cursor_name]
[{RETURNING|RETURN}
* | output_expression [[ AS] output_name] [, ...] INTO host_variable_list ]
Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the VALUES clause references an array or a pointer to an array.
table is the name (optionally schema-qualified) of an existing table. Include the ONLY clause to limit processing to the specified table; if you do not include the ONLY clause, any tables inheriting from the named table are also processed.
alias is a substitute name for the target table.
using_list is a list of table expressions, allowing columns from other tables to appear in the WHERE condition.
Include the WHERE clause to specify which rows should be deleted. If you do not include a WHERE clause in the statement, DELETE will delete all rows from the table, leaving the table definition intact.
condition is an expression, host variable or parameter marker that returns a value of type BOOLEAN. Those rows for which condition returns true will be deleted.
cursor_name is the name of the cursor to use in the WHERE CURRENT OF clause; the row to be deleted will be the one most recently fetched from this cursor. The cursor must be a non-grouping query on the DELETE statements target table. You cannot specify WHERE CURRENT OF in a DELETE statement that includes a Boolean condition.
The RETURN/RETURNING clause specifies an output_expression or host_variable_list that is returned by the DELETE command after each row is deleted:
output_expression is an expression to be computed and returned by the DELETE command after each row is deleted. output_name is the name of the returned column; include * to return all columns.
host_variable_list is a comma-separated list of host variables and optional indicator variables. Each host variable receives a corresponding value from the RETURNING clause.
For example, the following statement deletes all rows from the emp table where the sal column contains a value greater than the value specified in the host variable, :max_sal:
For more information about using the DELETE statement, please see the PostgreSQL Core documentation available at:


Table of Contents Previous Next