Categories:
DML Commands - General

DELETE

Remove data from a table using an optional WHERE clause and/or additional tables.

Important

Unlike TRUNCATE TABLE, this command does not delete the external file load history. If you delete rows loaded into the table from a staged file, you cannot load the data from that file again unless you modify the file and stage it again.

Syntax

DELETE FROM <table_name>
            [ USING <additional_tables> ]
            [ WHERE <condition_query> ]

Required Parameters

table_name
Specifies the table from which rows are removed.

Optional Parameters

USING additional_tables
Specifies one or more tables to use to select rows for removal. Note that repeating the target table will cause a self-join on the table to be processed.
WHERE condition_query
Specifies a query to use to select rows for removal. If this parameter is omitted, all rows in the table are removed (i.e. empty table), but the table remains.

Usage Notes

  • When deleting based on a JOIN (by specifying a USING clause), it is possible that a row in the target table joins against several rows in the USING table(s). If the DELETE condition is satisfied for any of the joined combinations, the target row is deleted.

    For example, given tables tab1 and tab2 with columns (k number, v number):

    select * from tab1;
    
    -------+-------+
       k   |   v   |
    -------+-------+
       0   |   10  |
    -------+-------+
    
    Select * from tab2;
    
    -------+-------+
       k   |   v   |
    -------+-------+
       0   |   20  |
       0   |   30  |
    -------+-------+
    

If you run the following query, the row in tab1 is joined against both rows of tab2:

DELETE FROM tab1 USING tab2 WHERE tab1.k = tab2.k

Because at least one joined pair satisfies the condition, the row is deleted. As a result, after the statement completes, tab1 is empty.

Examples

DELETE FROM tab1
  USING tab2
  WHERE tab1.key_column = tab2.tab1_key AND tab2.number_column < 10;