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: `DIRECT` `LABEL` For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.
`[database.]schema`	Specifies a schema, by default `public`. If schema is any schema other than `public`, you must supply the schema name. For example: 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, `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 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

DELETE removes data directly from ROS storage.

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 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.

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 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.