Delete From (Delta Lake on Azure 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.
Filter rows by predicate.
WHERE predicate supports subqueries, including
NOT EXISTS, and scalar subqueries. The following types of subqueries are not supported:
- Nested subqueries, that is, an subquery inside another subquery
NOT INsubquery 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
NOT IN subqueries can be slow.
DELETE FROM events WHERE date < '2017-01-01'
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')