DELETE

Delete rows from a table based on the results of a condition.

Note: The Yellowbrick column store is designed to optimally store, compress, and organize data for high-volume read-oriented analytics. As a result, when data is deleted it continues to exist in the system for a period of time until it can be efficiently removed and data can be reorganized. These optimizations are fully automatic and require no administration or manual interaction.
[ WITH name AS (subquery) [, ...] ]
DELETE FROM table_name [ * ] [ [ AS ] alias ]
    [ USING list ]
    [ WHERE condition ]
WITH
See WITH Clause for syntax details. You can reference a WITH subquery in the USING clause of the DELETE statement.
USING
Name one or more tables or table references, which in turn can be referenced in WHERE clause conditions for the DELETE operation. Do not list the target table for the DELETE (except for self-join purposes).
WHERE
Use this WHERE clause just as you would use the WHERE clause in a SELECT statement. Define a condition on either the target table or a table that is listed in the USING clause. For example, you can join the target table to another listed table to qualify rows for the DELETE operation.

DELETE FROM table without a WHERE clause deletes all of the rows from the table.

For example, delete rows from the match table that fall within a date range for the matchday column:
premdb=# delete from match 
where extract(year from matchday) between 1992 and 1999;
DELETE 3101
For example, delete rows from the match table based on a join to the season table:
premdb=# delete from match 
using season 
where match.seasonid=season.seasonid;
DELETE 8606
This example uses a WITH clause that contains a ROW_NUMBER function. This statement removes duplicates from the season table based on values in the seasonid column.
premdb=# with rows(rid, rn) as (select rowid, row_number() over(partition by seasonid order by seasonid) from season) 
delete from season using rows where rows.rn>1 and season.rowid=rows.rid;
DELETE 0