DELETE

Delete rows from a table.

note

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.

Syntax

DELETE FROM <table_name>
[ AT { REF[ERENCE] | BRANCH } <reference_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 { REF[ERENCE] | BRANCH } <reference_name> String   Optional

Only for Nessie sources. 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.

note

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.

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.

Example

Example DELETE command using USING and WHERE

DELETE 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;

note

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.

Example of an unsupported DELETE command

delete from orders
  using returns
  where orders.order_id = returns.order_id;

Equivalent DELETE command using a correlated subquery

delete from orders
  where exists(select 1 from returns where order_id = orders.order_id)