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

1
2
3
4

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 Hint-based Tuning.

• 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.

  

  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

1
2
3
4
5
6

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

1

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

1

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