UPDATE
Applies to: Databricks SQL
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
[ common_table_expression ]
UPDATE table_name [table_alias]
SET { { column_name | field_name } = [ expr | DEFAULT } [, ...]
[WHERE clause]
Parameters
-
Common table expressions (CTE) are one or more named queries which can be reused multiple times within the main query block to avoid repeated computations or to improve readability of complex, nested queries.
-
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. -
Define an alias for the table. The alias must not include a column list.
-
A reference to a column in the table. You may reference each column at most once.
-
A reference to field within a column of type STRUCT. You may reference each field at most once.
-
An arbitrary expression. If you reference
table_name
columns they represent the state of the row prior the update. -
DEFAULT
Applies to:
Databricks SQL
Databricks Runtime 11.3 LTS and above
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 anOR
, for example,a = 3 OR b NOT IN (SELECT c from t)
In most cases, you can rewrite
NOT IN
subqueries usingNOT EXISTS
. You should useNOT EXISTS
whenever possible, asUPDATE
withNOT 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'