DELETE FROM (Delta Lake on Azure Databricks)DELETE FROM (Delta Lake on Azure Databricks)

述語に一致する行を削除します。Deletes the rows that match a predicate. 述語が指定されていない場合、はすべての行を削除します。When no predicate is provided, deletes all rows.

構文Syntax

DELETE FROM table_identifier [AS alias] [WHERE predicate]
  • table_identifiertable_identifier

    • [database_name.] table_name: テーブル名。必要に応じて、データベース名で修飾します。[database_name.] table_name: A table name, optionally qualified with a database name.
    • delta.`<path-to-table>`: 既存のデルタテーブルの場所。delta.`<path-to-table>`: The location of an existing Delta table.
  • エイリアスとしてAS alias

    テーブルの別名を定義します。Define a table alias.

  • WHEREWHERE

    述語で行をフィルター処理します。Filter rows by predicate.

    述語は、、、、、 WHERE IN NOT IN EXISTS スカラーサブクエリを含むサブクエリをサポートし NOT 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 EXISTS ます。In 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')