DELETE
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The DELETE statement deletes rows from the specified table that satisfy the search_condition in the WHERE clause.
Caution! If the WHERE clause is omitted, the statement deletes all rows in the table. The result is a valid but empty table.
The DELETE statement does not automatically recover the space in a table left by the deleted rows. However, if new rows are added later, the empty space can be reused. To recover lost space after deleting many rows from a table, modify the table.
You can also delete all rows from a table, using
MODIFY...TO TRUNCATED (see
MODIFY).
The DELETE statement has the following formats:
Interactive and database procedure version:
[EXEC SQL] DELETE FROM [schema.]tablename [corr_name]
[WHERE search_condition];
Embedded non-cursor version:
[EXEC SQL] [REPEATED] DELETE FROM [schema.]tablename [corr_name]
[WHERE search_condition];
Embedded cursor version:
[EXEC SQL] DELETE FROM [schema.]tablename
WHERE CURRENT OF cursor_name;
tablename
Specifies the name of the table from which rows are to be deleted.
corr_name
Specifies a correlation name for the table for use in the search_condition.
Last modified date: 04/03/2024