DELETE
Delete rows from a table.
Dremio supports reading positional deletes and equality deletes for Apache Iceberg v2 tables. Dremio does not support reading global equality deletes from Apache Iceberg v2 tables in which the partition spec for the delete file is unpartitioned. Dremio performs writes using copy-on-write by default and supports writes using merge-on-read if specified in the Iceberg table properties.
DELETE FROM <table_name>
[ AT { REF[ERENCE] | BRANCH } <reference_name> ]
[ AS <alias> ]
[ USING <additional_table_or_query>
[ WHERE <where_conditions> ]
Parameters
<table_name> String
The name of the table with data that you want to delete.
AT { REF[ERENCE] | BRANCH } <reference_name> String Optional
Specifies the reference at which you want the table to be deleted from. When this parameter is omitted, the current branch is used.
REF: Identifies the specific reference.
BRANCH: Identifies the specific branch.
If you specify the reference for the target table, you must also specify the reference for the source table.
AS <alias> String Optional
The alias of the table.
<additional_table_or_subquery>String Optional
If you need to refer to additional tables or subqueries in the WHERE
clause to help identify the rows to be removed, then specify those table names in the USING
clause. Enclose subqueries in parentheses.
WHERE <where_conditions> String Optional
The filter for specifying which rows of the table to delete.
Examples
Example of aDELETE
command with a join between a target table (orders
) and a source table (returns
)
DELETE FROM orders
USING returns
WHERE orders.order_id = returns.order_id;
DELETE
command using a correlated subquery
DELETE FROM orders
WHERE EXISTS (select 1 from returns where order_id = orders.order_id)