Applies to: Databricks SQL Databricks Runtime
Deletes the rows that match a predicate. When no predicate is provided, deletes all rows.
This statement is only supported for Delta Lake tables.
Identifies an existing table. The name must not include a temporal specification.
table_namemust not be a foreign table.
Define an alias for the table. The alias must not include a column list.
Filter rows by predicate.
WHEREpredicate 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 INsubqueries using
NOT EXISTS. We recommend using
NOT EXISTSwhenever possible, as
NOT INsubqueries 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')