UPDATE

Update rows in a table.

note

Dremio supports reading Apache Iceberg v2 tables with positional deletes. Reading equality deletes is also supported for Apache Iceberg tables. Dremio does not support reading Apache Iceberg tables with global equality deletes, in which the delete file's partition spec is unpartitioned. Writes by Dremio are performed using copy-on-write. Writes using merge-on-read are not supported.

Syntax

UPDATE <table_name>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  [ AS <alias> ]
  SET <column1_name> = <value1> [, <column2_name> = <value2> ... ]
[ FROM <additional_table_or_query> [, <additional_table_or_query> ]
[ WHERE where_conditions ]

Parameters

<table_name> String

The name of the table with data that you want to update.

---

AT { REF[ERENCE] | BRANCH } <reference_name> String   Optional

Only for Nessie sources. Specifies the reference at which you want the table to be updated. When this parameter is omitted, the current branch is used.

REF: Identifies the specific reference.

BRANCH: Identifies the specific branch.

note

If AT BRANCH is specified, it will override the session context for the entire query including the SELECT portions.

note

If you specify the reference for the target table, you must also specify the reference for the source table.

---

AS <alias>

The alias of the table.

---

SET <column_name1> = <value1> [, <column_name2> = <value2> ... ] String

Sets the value of one or more columns. The value can be any expression or sub-query that returns a single value.

---

<additional_table_or_subquery> String   Optional

If you need to refer to additional tables or subqueries in the WHERE clause to help identify the rows to be updated or for setting new values, then specify those table names in the FROM clause. Enclose subqueries in parentheses.

When a WHERE clause contains a JOIN between source tables in the FROM clause and the target table, a row in the target table might join with more than one row in the source table. When this condition occurs, the UPDATE command fails with an error message.

---

WHERE where_conditions String   Optional

The filter for specifying which rows of the table to update.

Examples

Example UPDATE command using FROM and WHERE

UPDATE target
SET v = b.v + c.v
FROM src b , (select k, min(v) v from src_new group by k having min(v) > 10) c
WHERE target.k = b.k and target.k = c.k;