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

This statement is only supported for Delta Lake tables.


UPDATE table_name [table_alias]
   SET  { { column_name | field_name }  = [ expr | DEFAULT } [, ...]
   [WHERE clause]


  • table_name

    Identifies table to be updated. The table name must not use a temporal specification.

  • table_alias

    Define an alias for the table. The alias must not include a column list.

  • column_name

    A reference to a column in the table. You may reference each column at most once.

  • field_name

    A reference to field within a column of type STRUCT. You may reference each field at most once.

  • expr

    An arbitrary expression. If you reference table_name columns they represent the state of the row prior the update.


    Since: Databricks Runtime 11.2

    The DEFAULT expression for the column if one is defined, NULL otherwise.


    Filter rows by predicate. The WHERE clause may include subqueries with the following exceptions:

    • 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. You should use NOT EXISTS whenever possible, as UPDATE with NOT IN subqueries can be slow.


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

> 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')

> UPDATE events
     SET ignored = DEFAULT
  WHERE eventType = 'unknown'