DELETE
The DELETE command deletes rows from a table.
Dremio supports reading Apache Iceberg v2 tables with positional deletes. Reading tables with equality deletes is not supported. Writes by Dremio are performed using copy-on-write. Writes using merge-on-read are not supported.
DELETE FROM <table_path>.<table_name> [AS alias]
[ USING <additional_table_or_query> [, <additional_table_or_query> ] ]
[ WHERE where_conditions ]
Parameters
<table_name>
String
The name of the table with data that you want to delete.
AT BRANCH <branch_name>
String
Optional
Specifies the branch where you want the table to be deleted from. When this parameter is omitted, the current branch is used.
<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.
When a WHERE clause contains a JOIN between source tables in the USING clause and the target table, a row in the target table might join with more than one row in the source table. When this condition occurs, the DELETE command fails with an error message.
WHERE where_conditions
String
Optional
The filter for specifying which rows of the table to delete.
IN <catalog_name>
String
Optional
The name of the Arctic catalog where the table is located. If not specified, the current Arctic catalog in the query context is used.
Example
Example DELETE command using USING and WHEREDELETE FROM target
USING src b , (select k, min(v) v from src_new group by k having min(v) < 10) c
WHERE target.k = b.k and target.k = c.k;
For Dremio v22 and earlier: Join conditions are not supported in WHERE clauses. If you need to use a join condition, use a correlated subquery instead.
delete from orders
using returns
where orders.order_id = returns.order_id;
delete from orders
where exists(select 1 from returns where order_id = orders.order_id)