- DML Commands - General
Remove data from a table using an optional WHERE clause and/or additional tables.
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.
DELETE FROM <table_name> [ USING <additional_tables> ] [ WHERE <condition_query> ]
- Specifies the table from which rows are removed.
- 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.
- 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.
When deleting based on a JOIN (by specifying a
USINGclause), it is possible that a row in the target table joins against several rows in the
USINGtable(s). If the DELETE condition is satisfied for any of the joined combinations, the target row is deleted.
For example, given tables
(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
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.
DELETE FROM tab1 USING tab2 WHERE tab1.key_column = tab2.tab1_key AND tab2.number_column < 10;