DELETE

Purpose

Use this statement to delete rows in a table.

Prerequisites

  • You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

  • You need either the system privilege DELETE ANY TABLE or the object privilege DELETE on the table or its schema, or the table must be owned by your or one of your assigned roles.

Syntax

delete::=

DELETE syntax diagram 1

DELETE syntax diagram 2

Usage Notes

  • Internally, the rows are not immediately deleted but marked as deleted. When a certain threshold is reached (default is 25% of the rows), these rows are finally dropped. Therefore DELETE statements can have varying execution times. The current percentage of marked rows can be found in system tables EXA_*_TABLES (for example, EXA_ALL_TABLES) in the column DELETE_PERCENTAGE.
  • If a row is marked as deleted, it is not possible to recover the data.
  • The PREFERRING clause defines a Skyline preference term. For more information, refer to the Skyline section.

Examples

DELETE FROM staff WHERE name='SMITH';
DELETE FROM staff;