INSERT Statement

SQL Statements

SELECT INTO Statement

DELETE Statement

statement_DELETE ::= DELETE FROM [scheme.]table_name [ WHERE condition ]

Description

The DELETE statement (or Searched DELETE) deletes records from a specified table. If a condition is specified, only records that fulfill the condition are deleted, otherwise, the statement deletes all records in the table. Deleted records may be restored or dropped (automatic dropping of deleted records may be set using the SQLOPT_EXPLIC_FREE compatibility attribute). The number of deleted records may be retrieved using the system property @@ROWCOUNT.

If the table is prefixed with an application name on the same server, the table from the specified application will be used.

You may use this statement with a fixed query (VIEW) that allows record deletion.

The DELETE statement executes the DELETE trigger for each deleted record. BEFORE DELETE is executed BEFORE the record is deleted and AFTER DELETE is executed after the record is deleted. If an error occurs in the trigger, the entire DELETE is rolled back.

If the table the DELETE statement is executed on is a parent table in a active referential integrity (RI), two actions may occur:

• If the ON DELETE CASCADE, SET NULL or SET DEFAULT is used in the referential integrity definition, the defined action is performed on records in the child table.
• The error 40006 (REFERENTIAL_CONSTRAIN) occurs. This also causes a default rollback.

Deleting records requires sufficient privileges to do so.

Quick Deletion of All Records in a Table

This statement is generally used when you need to delete all records in a table. If the table contains an index, it is faster to delete the index first using the DROP INDEX statement, execute the DELETE statement (drop unreleased records using the CALL Free_deleted) and then rebuild the index. This method cannot be used if the index is in a referential integrity definition.

Since version 8.1 there exists a special function called Truncate_table that can delete records.

Example

Delete ALL records that belong to department 1.

DELETE FROM Employed WHERE Department = 1

Delete ALL records that have the column number value higher than the average.

DELETE FROM Tab3
 WHERE number > (SELECT Avg(number) FROM Tab3);

See

• INSERT
• UPDATE
• DELETE (Positioned)

INSERT Statement

SQL Statements

SELECT INTO Statement