Deletes zero, one, or more rows from the specified table that satisfy the search condition specified in the WHERE clause. If the optional WHERE clause is not specified, then the DELETE statement deletes all rows of the specified table.

When deleting row(s) of a multi-tenant table, a regular tenant can only delete rows in its partition, but a super-tenant or a DBA can delete rows in all the tenant partitions and group partitions. A super-tenant or a DBA may refine which tenants' rows must be affected by using the tenantid_tbl() or the tenantName_tbl() function in the WHERE clause search_condition.

While deleting a row of a partitioned table, if the row belongs to a read-only partition, the DELETE statement returns an error.

Syntax

DELETE FROM [owner_name]{table_name|view_name}[ WHERE search_condition];

Notes

If the table has primary or candidate keys and there are references from other tables to the rows to be deleted, the statement is rejected.

Examples

DELETE statement

The following example illustrates the DELETE statement:

DELETE FROM Customer WHERE Name = 'Surf and Sport';

Deleting a row from a multi-tenant table

The following example deletes the row from a multi-tenant table using custnum and tenantid_tbl() in the WHERE clause.

DELETE FROM mtcustomer
WHERE custnum = 1428 AND tenantName_tbl (pub.mtcustomer) = 'SNCSoftware';