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 (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.
Vertica's implementation of DELETE differs from traditional databases: it does not delete data from disk storage; it marks rows as deleted so they are available for historical queries.
DELETE [ /*+LABEL (label‑string)*/ ] FROM [[database.]schema.]table [ where-clause ]
Assigns a label to a statement to identify it for profiling and debugging.
Database and schema. The default schema is
Any table, including temporary tables.
Specifies which rows to mark for deletion. If you omit this clause,
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
DELETEstatement includes a
SETclause that specifies columns from that table.
You cannot execute
DELETE on a projection.
Committing INSERT, UPDATE, and DELETE
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.
Deleting from Persistent Tables
DELETE removes data directly from the ROS.
Deleting from a Temporary Table
DELETE execution on temporary tables varies, depending on whether the table was created with
ON COMMIT DELETE ROWS (default) or
ON COMMIT PRESERVE ROWS:
WHEREclause that specifies which rows to remove, behavior is identical:
DELETEmarks the rows for deletion. In both cases, you cannot roll back to an earlier savepoint.
WHEREclause 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,
TRUNCATE TABLEand removes all rows from storage.
If you issue an unqualified
DELETEstatement on a temporary table created with
ON COMMIT DELETE ROWS, Vertica removes all rows from storage but does not end the transaction.
The following command removes all rows from temporary table
=> DELETE FROM temp1;
The following command deletes all records from anchor table
C1 = C2 - C1.
=> DELETE FROM T WHERE C1=C2-C1;
The following command deletes all records from the customer table in the retail schema where the state attribute is in MA or NH:
=> 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 in Analyzing Data.