UPDATE

Function

UPDATE updates data in a table. UPDATE changes the values of the specified columns in all rows that satisfy the condition. The WHERE clause clarifies conditions. The columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

Precautions

• You must have the UPDATE permission on a table to be updated.
• You must have the SELECT permission on all tables involved in the expressions or conditions.
• The distribution column of a table cannot be modified.
• For column-store tables, the RETURNING clause is currently not supported.
• Column-store tables do not support non-deterministic update. If you update data in one row with multiple rows of data in a column-store table, an error is reported.
• Memory space that records update operations in column-store tables is not reclaimed. You need to clean it by executing VACUUM FULL table_name.
• You are not advised to create a table that needs to be frequently updated as a replication table.
• Column-store tables support lightweight UPDATE operations. Lightweight UPDATE operations only rewrite the updated columns to reduce space usage. Lightweight UPDATE for column-store tables is controlled by GUC parameter enable_light_colupdate. It is disabled by default.
• Column-store lightweight UPDATE is unavailable and automatically changes to the regular UPDATE operation in the following scenarios: updating an index column, updating a primary key column, updating a partition column, updating a PCK column, and online scaling.
• There is a low probability that an error is reported when lightweight UPDATE and backend column-store AUTOVACUUM coexist. You can run ALTER TABLE to set the table-level parameter enable_column_autovacuum_garbage to off to avoid this issue. If the table-level parameter enable_column_autovacuum_garbage is set to off, the backend column-store AUTOVACUUM of the table is disabled.

• Avoid using UPDATE or DELETE to modify or delete a large volume of data and use TRUNCATE PARTITION or DROP PARTITION instead.
• Do not concurrently update the same column-store table using the UPDATE and UPSERT statements.
• For more information about development and design specifications, see Development and Design Proposal.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

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

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

• partition_name

  Specifies the name of a partition. Only clusters of version 8.2.1 or later support this option.

  Value range: An existing partition name.

• partition_key_value

  Specifies the key value of a partition.

  The value specified by PARTITION FOR ( partition_key_value [, ...] ) can uniquely identify a partition.

  Value range: value range of the partition key for the partition to be renamed

• column_name

  Renames a column.

  You can refer to this column by specifying the table name and column name of the target table. Example:

  1	UPDATE foo SET foo.col_name = 'GaussDB';

  You can refer to this column by specifying the target table alias and the column name. For example:

  1	UPDATE foo AS f SET f.col_name = 'GaussDB';

  Value range: an existing column name

• expression

  An expression or value to assign to the column.

• DEFAULT

  Sets the column to its default value.

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

• sub_query

  Specifies a subquery.

  This command can be executed to update a table with information for other tables in the same database. For details about clauses in the SELECT statement, see SELECT.

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

• output_expression

  An expression to be computed and returned by the UPDATE command after each row is updated.

  Value range: The expression can use any column names of the table named by table_name or table(s) listed in FROM. Write * to return all columns.

• output_name

  A name to use for a returned column.

Examples

Update the values of all records.

1

UPDATE reason SET r_reason_sk = r_reason_sk * 2;

If the WHERE clause is not included, all r_reason_sk values are updated.

1

UPDATE reason SET r_reason_sk = r_reason_sk + 100;

Redefine r_reason_sk whose r_reason_desc is reason2 in the reason table.

1

UPDATE reason SET r_reason_sk = 5 WHERE r_reason_desc = 'reason2';

Redefine r_reason_sk whose value is 2 in the reason table.

1

UPDATE reason SET r_reason_sk = r_reason_sk + 100 WHERE r_reason_sk = 2;

Redefine the course IDs whose r_reason_sk is greater than 2 in the reason table.

1

UPDATE reason SET r_reason_sk = 201 WHERE r_reason_sk > 2;

You can run an UPDATE statement to update multiple columns by specifying multiple values in the SET clause. For example:

1

UPDATE reason SET r_reason_sk = 5, r_reason_desc = 'reason5' WHERE r_reason_id = 'fourth'; 

Update partition p1 in the partitioned table test_range_row:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE test_range_row(a int, d int)
DISTRIBUTE BY hash(a) PARTITION BY RANGE(d)
(
    PARTITION p1 values LESS THAN (60),
    PARTITION p2 values LESS THAN (75),
    PARTITION p3 values LESS THAN (90),
    PARTITION p4 VALUES LESS THAN (maxvalue)
);
INSERT OVERWRITE INTO test_range_row PARTITION(p1) VALUES(55,51);
INSERT OVERWRITE INTO test_range_row PARTITION(p3) VALUES(85,80);

UPDATE test_range_row PARTITION(p1) SET d = 41;