delete

Delete one or more rows from a table

Syntax

delete_stmt:
          DELETE [database_name.]table_name [[AS] correlation_name] 
               [WHERE {conditional_expr | CURRENT OF cursor_name}]

Description

This statement deletes one or more rows from table table_name. Two types of DELETE are supported. In a searched delete, the DELETE statement deletes all rows of the table that satisfy the conditional expression (conditional_expr) specified in the WHERE clause. In a positioned delete, the DELETE statement deletes the current row associated with the specified cursor (cursor_name) in the WHERE current of clause. The cursor_name must have been established through a prior call to either SQLGetCursorName or a SQLSetCursorName on a compiled, updateable SELECT statement associated with a separate statement handle.

Deleting rows that have referencing foreign keyed rows will either succeed or fail based on the CASCADE or RESTRICT settings associated with the related foreign key specifications. If all referenced rows specify CASCADE then all of the referencing rows will be deleted in addition to the rows from this particular table. However, if the RESTRICT option is specified and referencing rows exist, then the DELETE will fail with a referential integrity error. Note also that while a foreign key to this table may have CASCADE set a foreign key to the referencing table may itself have RESTRICT set and thus the cascaded deletion could cause the DELETE to fail due to a referential integrity constraint violation.

A call to SQLRowCount after a successful execution of DELETE will return the count of all rows from all affected (i.e., cascaded) tables that were deleted.

Example

delete from book where date_sold < date "2003-01-01";
	...
delete from sponsor where state < "A" or state > "Z";
	...
delete from person where current of SQL_CUR_f3f0_08b0;