UPDATE (Delta Lake on Databricks)

Updates the column values for the rows that match a predicate. When no predicate is provided, update the column values for all rows.

Syntax 

UPDATE table_identifier [AS alias] SET col1 = value1 [, col2 = value2 ...] [WHERE predicate]

  • table_identifier

    • [database_name.] table_name: A table name, optionally qualified with a database name.
    • delta.`<path-to-table>` : The location of an existing Delta table.

  • AS alias

    Define a table alias.

  • WHERE

    Filter rows by predicate.

Example 

UPDATE events SET eventType = 'click' WHERE eventType = 'clk'

UPDATE supports subqueries in the WHERE predicate, including IN, NOT IN, EXISTS, NOT EXISTS, and scalar subqueries.

Subquery Examples 

UPDATE all_events
  SET session_time = 0, ignored = true
  WHERE session_time < (SELECT min(session_time) FROM good_events)

UPDATE orders AS t1
  SET order_status = 'returned'
  WHERE EXISTS (SELECT oid FROM returned_orders WHERE t1.oid = oid)

UPDATE events
  SET category = 'undefined'
  WHERE category NOT IN (SELECT category FROM events2 WHERE date > '2001-01-01')

Note

The following types of subqueries are not supported:

  • Nested subqueries, that is, a subquery inside another subquery
  • A 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 UPDATE with NOT IN subqueries can be slow.