Delete From (Delta Lake on Databricks)

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.

WHERE: Filter rows by predicate.

The WHERE predicate supports subqueries, including IN, NOT IN, EXISTS, NOT EXISTS, and scalar subqueries. 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.

Example

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

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')