UPDATE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

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.

Syntax 

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

Parameters

  • table_name

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

    table_name must not be a foreign table.

  • 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.

  • DEFAULT

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

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

  • WHERE

    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.

Examples 

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