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;