UPDATE

Function

Updates data in a table. The UPDATE statement modifies specified column values in all rows that meet a given condition. The WHERE clause defines the condition, and fields listed in the SET clause are updated. Columns not included retain their original values.

Currently, only Iceberg tables support this UPDATE operation.

Precautions

• To update data in a table, you must have UPDATE permissions on it. Additionally, you need SELECT permissions for any tables referenced in expressions or conditions within the query.
• Attempting to update the same row multiple times within a single SQL statement will result in an error.

Do not concurrently update the same partition of a table using UPDATE.

Syntax

UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ]
 SET {column_name = { expression | DEFAULT } 
    |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
    [ FROM from_list] [ WHERE condition ];

where sub_query can be:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]

Parameter Description

• plan_hint: specified as /*+ */, which provides hints for optimizing the execution plan of the statement block. Refer to Hint-based Tuning for detailed usage.
• table_name: name of the table to be updated, optionally qualified with its schema.
• alias: alias of the target table.
• column_name: name of the column to modify.
  • You can reference the column using the table name followed by the column name. For example:

    UPDATE foo SET foo.col_name = 'a'

  • Alternatively, use the table's alias followed by the column name. For example:

    UPDATE foo AS f SET f.col_name = 'a'

• expression: A value or expression assigned to the column.
• DEFAULT: Populates the column with its default value. If no default value is available, the value is NULL.
• sub_query: A subquery used to update a table based on information from other tables within the same database. For details on the SELECT clause, refer to SELECT.
• from_list: A list of table expressions allowing the use of columns from other tables in the WHERE condition. This is similar to declaring a table list in the FROM clause of a SELECT statement.

  Note: The target table must never appear in the from_list, except when performing a self-join (in which case it must appear under an alias).

• condition: A boolean expression determining which rows are updated. Only rows where the expression evaluates to true are updated.

Examples

Create the reason_update table.

CREATE TABLE reason_update
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
) store as iceberg;

Insert data to the reason_update table.

INSERT INTO reason_update VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB');

Perform the UPDATE operation on the reason_update table.

UPDATE reason_update SET TABLE_NA = 'TeacherD' where TABLE_SK = 3;