On this page

    Deleting Apache Iceberg Tables

    The DELETE command deletes rows from a table.

    Syntax
    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.


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

    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)