UPDATE

Function

Update specified data in an HStore table.

Precautions

  • Similar to column storage, the UPDATE operation on an HStore table in the current version involves DELETE and INSERT. You can configure a global GUC parameter to control the lightweight UPDATE of HStore. In the current version, the lightweight UPDATE is disabled by default.

  • In concurrent update scenarios, operations on the same CU will cause lock conflicts in traditional column-store tables and result in low performance. For HStore tables, the operations can be concurrently performed, and the update performance can be more than 100 times that of column-store tables.

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 ];

Parameters

  • plan_hint clause

    Following the keyword in the /*+ */ format, hints are used to optimize the plan generated by a specified statement block. For details, see "Performance Tuning > Query Improvement > Hint-based Tuning" in Data Warehouse Service (DWS) Developer Guide.

  • table_name

    Name (optionally schema-qualified) of the table to be updated.

    Value range: an existing table name

  • alias

    Specifies the alias for the target table.

    Value range: a string. It must comply with the naming convention.

  • expression

    Specifies a value assigned to a column or an expression that assigns the value.

  • DEFAULT

    Sets the column to its default value.

    The value is NULL if no specified default value has been assigned to it.

  • from_list

    A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM clause of a SELECT statement.

    Important

    Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

  • condition

    An expression that returns a value of type boolean. Only rows for which this expression returns true are updated.

Example

Create the reason_update table.

CREATE TABLE reason_update
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
)WITH(ORIENTATION=COLUMN, ENABLE_HSTORE=ON);

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;