You use DELETE to delete rows from a table.
If you want to delete a row from the specified table, you must own the table or have the DELETE privilege for this table. Furthermore, the transaction mode of the current transaction must be READ WRITE.
If integrity constraints have been defined for the table or columns involved, these are checked after the delete operation has been performed. If the integrity constraint has been violated, the deletion is cancelled and an appropriate SQLSTATE set.
DELETE FROM table [[AS] correlation_name ]
[WHERE { search_condition | CURRENT OF cursor }]
table
Name of the table from which rows are to be deleted. The table can be a base table or an updatable view.
correlation_name
Table name used in the search_condition as a new name for the table table.
The correlation_name must be used to qualify the column name in every column specification that references the table table if the column name is not unambiguous.
The new name must be unique, i.e. correlation_name can only occur once in a table specification of this search condition.
You must give a table a new name if the columns in the table cannot be identified otherwise uniquely.
In addition, you may give a table a new name in order to formulate an expression so that it is more easily understood or to abbreviate long names.
WHERE clause
Indicates the rows to be deleted.
WHERE clause omitted:
All the rows in the table are deleted.
search_condition
Condition that the rows to be deleted must satisfy. A row is only deleted if it satisfies the specified search condition.
Table specification in search_condition that are outside of subqueries can only reference the specified table.
Subqueries in search_condition cannot reference the base table from which the rows are to be deleted either directly or indirectly.
CURRENT OF cursor
Name of the cursor used to select the rows to be deleted. The cursor must be updatable (see section "Defining a cursor") and table must be the underlying table.
The cursor must be declared in the same compilation unit. It must be open. It must be positioned on a row in the derived table with FETCH before the DELETE statement is issued.
DELETE deletes the row at the current cursor position from table.
After DELETE, the cursor is positioned before the next row in the derived table or after the last row if the end of the table has been reached. If you want to execute another DELETE...WHERE CURRENT OF statement, you must first position the cursor on a row in the derived table with FETCH.
DELETE is not permitted if block mode is activated for the open cursor cursor (see section "PREFETCH pragma").
If a cursor is defined with the WITH HOLD clause, a DELETE statement may not be issued until a FETCH statement has been executed for this cursor in the same transaction.
DELETE and transaction management
DELETE initiates an SQL transaction outside routines if no transaction is open. If you define an isolation level, you can control what effect this DELETE statement has on concurrent transactions (see section "SET TRANSACTION - Define transaction attributes").
If an error occurs during the DELETE statement, any deletions already performed are canceled.
Examples
Delete all customers situated in Hanover from the CUSTOMERS table.
DELETE FROM customers WHERE city = 'Hanover'
All customers for whom USA is entered as the country in the CUSTOMERS table are to be deleted from the CONTACTS table. The statement is only executed if the referential constraint CON_REF_CONTACTS in the ORDERS table is not violated.
DELETE FROM contacts WHERE cust_num = (SELECT cust_num FROM customers WHERE country='USA')
Use a cursor to delete customers situated in Hanover from the CUSTOMERS table.
DECLARE cur_customers CURSOR FOR SELECT cust_num, company, city FROM customers WHERE city = 'Hanover' FOR UPDATE OPEN cur_customers
All the rows found can then be deleted with a series of FETCH and DELETE statements.
FETCH cur_customers INTO :CUSTNUM, :COMPANY, :CITY DELETE FROM customers WHERE CURRENT OF cur_customers
Use a cursor to select all cancelled orders (ORDER_STAT = 5) from the ORDERS table. The entries for these orders are then deleted in the SERVICE and ORDERS tables.
DECLARE cur_order1 CURSOR FOR SELECT order_num, order_text FROM orders WHERE order_stat = 5 FOR UPDATE FETCH cur_order1 INTO :ORDERS.ORDER_NUM DELETE FROM orders WHERE CURRENT OF cur_order1 DELETE FROM service WHERE order_num = :ORDERS.ORDER_NUM
See also
INSERT, UPDATE