Dremio Cloud
Dremio Software
 
On this page

    Updating Apache Iceberg Tables

    The UPDATE command updates rows in a table.

    Syntax 
    UPDATE <table_path>.<table_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.

    SET <column1_name> = <value1> [, <column2_name> = <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 USING clause. Enclose subqueries in parentheses.

    When a WHERE clause contains a JOIN between source tables in the USING 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.

    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;

    For Dremio v22 and earlier:

    Join conditions are not supported in WHERE clauses. If you need to use a join condition, use a MERGE statement.

    Example of an unsupported UPDATE command 
    update t2 set id = id + t1.id from t1 where t1.name = t2.name;
    Equivalent MERGE command 
    merge into t2 using t1 on (t1.name = t2.name) when matched then update set id = t2.id + t1.id;