Skip to main content

DELETE

Delete rows from a table.

Syntax 
DELETE FROM <table_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.

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 a DELETE 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;
Equivalent DELETE command using a correlated subquery 
DELETE FROM orders
  WHERE EXISTS (select 1 from returns where order_id = orders.order_id)

Usage Notes​

By default, DELETE uses copy-on-write mode, which rewrites data files. You can configure the table to use merge-on-read mode for faster deletes by setting the write.delete.mode table property. See Apache Iceberg table properties for details.