Delete (Databricks Delta)

DELETE FROM [db_name.]table_name [AS alias] [WHERE predicate]

Delete the rows that match a predicate. When no predicate is provided, delete all rows.

Example

DELETE FROM events WHERE date < '2017-01-01'

DELETE supports subqueries in the WHERE predicate, including IN, NOT IN, EXISTS, NOT EXISTS, and scalar subqueries.

Subquery Examples

DELETE FROM all_events
  WHERE session_time < (SELECT min(session_time) FROM good_events)

DELETE FROM orders AS t1
  WHERE EXISTS (SELECT oid FROM returned_orders WHERE t1.oid = oid)

DELETE FROM events
  WHERE category NOT IN (SELECT category FROM events2 WHERE date > '2001-01-01')

Note

The following types of subqueries are not supported:

  • Nested subqueries, that is, an subquery inside another subquery
  • NOT IN subquery inside an OR, for example, a = 3 OR b NOT IN (SELECT c from t)

In most cases, you can rewrite NOT IN subqueries using NOT EXISTS. We recommend using NOT EXISTS whenever possible, as DELETE with NOT IN subqueries can be slow.