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:

Important: 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
TRANSACTION<transaction>	Name of the transaction under control of which the statement is executed; SQL only
<table>	Name of the table from which to delete rows
WHERE<search_condition>	Search condition that specifies the rows to delete; without this clause, DELETE affects all rows in the specified table or view
WHERE CURRENT OF <cursor>	Specifies that the current row in the active set of <cursor> is to be deleted
ORDER BY <order_list>	Specifies columns to order, either by column name or ordinal number in the query, and the sort order (ASC or DESC) for the returned rows
ROWS <value> [TO <upper_value>] [BY <step_value>] [PERCENT][WITH TIES]	<value> is the total number of rows to return if used by itself <value> is the starting row number to return if used with TO <value> is the percent if used with PERCENT <upper_value> is the last row or highest percent to return If <step_value> = <n>, returns every <n>th row, or <n> percent rows PERCENT causes all previous ROWS values to be interpreted as percents WITH TIES returns additional duplicate rows when the last value in the ordered sequence is the same as values in subsequent rows of the result set; must be used in conjunction with ORDER BY

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.

Important: 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;

See Also

• DECLARE CURSOR
• FETCH
• GRANT
• OPEN
• REVOKE
• SELECT
• Embedded SQL Guide