DELETE

Removes the specified rows from a table and returns a count of the deleted rows.

Removes the specified rows from a table and returns a count of the deleted rows. A count of 0 is not an error, but indicates that no rows matched the condition. An unqualified DELETE statement (one that omits a WHERE clause) removes all rows but leaves intact table columns, projections, and constraints.

DELETE supports subqueries and joins, so you can delete values in a table based on values in other tables.

Important

The Vertica implementation of DELETE differs from traditional databases: it does not delete data from disk storage, but instead marks rows as deleted so they are available for historical queries. Deleted data remains on disk, and counts against disk quota, until purged.

Syntax

DELETE [ /*+LABEL (label-string)*/ ]  FROM [[database.]schema.]table [ where-clause ]

Arguments

LABEL: Assigns a label to a statement to identify it for profiling and debugging.
[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
table: Any table, including temporary tables.
where-clause: Which rows to mark for deletion. If you omit this clause, DELETE behavior varies depending on whether the table is persistent or temporary. See below for details.

Privileges

Table owner or user with GRANT OPTION is grantor.

DELETE privilege on table
USAGE privilege on the schema of the target table
SELECT privilege on a table when the DELETE statement includes a WHERE or SET clause that specifies columns from that table.

Restrictions

You cannot execute DELETE on an immutable table.

Committing successive table changes

Vertica follows the SQL-92 transaction model, so successive INSERT, UPDATE, and DELETE statements are included in the same transaction. You do not need to explicitly start this transaction; however, you must explicitly end it with COMMIT, or implicitly end it with COPY. Otherwise, Vertica discards all changes that were made within the transaction.

Persistent and temporary tables

When deleting from a persistent table, DELETE removes data directly from the ROS.

DELETE execution on temporary tables varies, depending on whether the table was created with ON COMMIT DELETE ROWS (default) or ON COMMIT PRESERVE ROWS:

If DELETE contains a WHERE clause that specifies which rows to remove, behavior is identical: DELETE marks the rows for deletion. In both cases, you cannot roll back to an earlier savepoint.
If DELETE omits a WHERE clause and the table was created with ON COMMIT PRESERVE ROWS, Vertica marks all table rows for deletion. If the table was created with ON COMMIT DELETE ROWS, DELETE behaves like TRUNCATE TABLE and removes all rows from storage.

Note
If you issue an unqualified DELETE statement on a temporary table created with ON COMMIT DELETE ROWS, Vertica removes all rows from storage but does not end the transaction.

Examples

The following statement removes all rows from a temporary table:

=> DELETE FROM temp1;

The following statement deletes all records from a schema-qualified table where a condition is satisfied:

=> DELETE FROM retail.customer WHERE state IN ('MA', 'NH');

For examples that show how to nest a subquery within a DELETE statement, see Subqueries in UPDATE and DELETE.