DELETE
Go Up to Statement and Function Reference (Language Reference Guide)
Removes rows in a table or in the active set of a cursor. Available in gpre
, DSQL, and isql
.
SQL and DSQL form:
Omit the terminating semicolon for DSQL.
DELETE [TRANSACTION transaction] FROM table
{[WHERE search_condition] | WHERE CURRENT OF cursor}
[ORDER BY order_list]
[ROWS value [TO upper_value] [BY step_value][PERCENT][WITH TIES]];
search_condition = Search condition as specified in SELECT.
isql
form:
DELETE FROM TABLE [WHERE search_condition];
Argument | Description |
---|---|
|
Name of the transaction under control of which the statement is executed; SQL only |
<table> |
Name of the table from which to delete rows |
|
Search condition that specifies the rows to delete; without this clause, |
|
Specifies that the current row in the active set of <cursor> is to be deleted |
|
Specifies columns to order, either by column name or ordinal number in the query, and the sort order ( |
|
|
DELETE
specifies one or more rows to delete from a table or updatable view. DELETE
is one of the database privileges controlled by the GRANT
and REVOKE
statements.
The TRANSACTION
clause can be used in multiple transaction SQL applications to specify which transaction controls the DELETE
operation. The TRANSACTION
clause is not available in DSQL or isql
.
For searched deletions, the optional WHERE
clause can be used to restrict deletions to a subset of rows in the table.
Without a
WHERE
clause, a searched delete removes all rows from a table.When performing a positioned delete with a cursor, the WHERE CURRENT OF
clause must be specified to delete one row at a time from the active set.
Examples: The following isql
statement deletes all rows in a table:
DELETE FROM EMPLOYEE_PROJECT;
The next embedded SQL statement is a searched delete in an embedded application. It deletes all rows where a host-language variable equals a column value.
EXEC SQL
DELETE FROM SALARY_HISTORY
WHERE EMP_NO = :emp_num;
The following embedded SQL statements use a cursor and the WHERE CURRENT OF
option to delete rows from CITIES
with a population less than the host variable, min_pop. They declare and open a cursor that finds qualifying cities, fetch rows into the cursor, and delete the current row pointed to by the cursor.
EXEC SQL
DECLARE SMALL_CITIES CURSOR FOR
SELECT CITY, STATE
FROM CITIES
WHERE POPULATION < :min_pop;
EXEC SQL
OPEN SMALL_CITIES;
EXEC SQL
FETCH SMALL_CITIES INTO :cityname, :statecode;
WHILE (!SQLCODE)
{EXEC SQL
DELETE FROM CITIES
WHERE CURRENT OF SMALL_CITIES;
EXEC SQL
FETCH SMALL_CITIES INTO :cityname, :statecode;}
EXEC SQL
CLOSE SMALL_CITIES;