从 Azure Databricks 上的 (增量 Lake 中删除) 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.

WHERE

按谓词筛选行。Filter rows by predicate.

WHERE谓词支持子查询,包括 INNOT INEXISTSNOT EXISTS 和标量子查询。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 中的子查询 OR ,例如 a = 3 OR b NOT IN (SELECT c from t)NOT IN subquery inside an OR, for example, a = 3 OR b NOT IN (SELECT c from t)

在大多数情况下,可以使用重写 NOT IN 子查询 NOT EXISTSIn most cases, you can rewrite NOT IN subqueries using NOT EXISTS. 建议尽可能使用 NOT EXISTS ,因为 DELETE NOT IN 子查询可能会很慢。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')