DELETE
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.
Syntax
DELETE [ /*+ hint[, hint] */ ] FROM [[database.]schema.]table [ where-clause ]
Parameters
/*+ hint[, hint] */
|
One or both of the following hints: |
[database.]schema
|
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. |
table |
Any table, including temporary tables. |
where‑clause |
Specifies which rows to mark for deletion. If you omit this clause, |
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 aWHERE
orSET
clause that specifies columns from that table.
Restrictions
- You cannot execute
DELETE
on a projection. - DELETE returns an error if both of the following conditions are true:
- The table's partition expression includes a GROUP BY clause—for example, hierarchically partitioned tables.
- The table has projections that aggregate data—for example, live aggregate or Top-K projections.
Deleting from Persistent Tables
Unlike TRUNCATE TABLE
, DELETE
does not delete data from disk storage. Rather, it marks rows for deletion in the WOS. These rows are no longer valid in the current epoch. By default, DELETE
uses WOS. If WOS fills up, the operation overflows to 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
:
- If
DELETE
contains aWHERE
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 aWHERE
clause and the table was created withON COMMIT PRESERVE ROWS
, Vertica marks all table rows for deletion. If the table was created withON COMMIT DELETE ROWS
,DELETE
behaves likeTRUNCATE TABLE
and removes all rows from storage.If you issue an unqualified
DELETE
statement on a temporary table created withON COMMIT DELETE ROWS
, Vertica removes all rows from storage but does not end the transaction.
Examples
The following command removes all rows from temporary table temp1
:
=> DELETE FROM temp1;
The following command deletes all records from anchor table T
where 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.