UPDATE

Description

UPDATE updates the data in a table, modifying the values of the specified columns in all rows that meet the conditions. The WHERE clause specifies the conditions. Only the columns specified in the SET clause will be modified.

Precautions

• The table owner, users with the UPDATE permission on the current table, and users with the UPDATE ANY TABLE permission can update data in a table. System administrators automatically have this permission when separation of duties is disabled.
• UPDATE...... LIMIT row_count supports only some scenarios of execution plan pushdown. (Table replication is not supported.) The prerequisites are that the filter criteria must contain the equivalent distribution key and the filter criteria should be relatively simple. Do not use forcible type conversion. If the command fails to be executed, simplify the filter criteria.
• You must have the SELECT permission on all tables involved in expression or condition.
• The distribution keys can be updated only when enable_update_distkey is set to on and constraints are met. Otherwise, distribution keys do not support the UPDATE operation.
  

  The constraints on updating distribution keys are as follows:

  • Distribution keys can be updated only when enable_update_distkey is set to on.
  • The UPDATE statement cannot be pushed down to DNs for execution. The PGXC plan is directly generated and is not changed based on the values before and after the distribution key is updated.
  • Tables with row-level UPDATE TRIGGER are not supported. Otherwise, the execution fails and an error message is displayed. The row-level INSERT/DELETE TRIGGER does not take effect, and the update-statement-level TRIGGER is executed normally.
  • Concurrent update of the same row is not supported. You need to obtain a lock first. The result (returning 0 or reporting an error) of obtaining a lock on DNs depends on the setting of the GUC parameter concurrent_dml_mode. An error will be reported in the following cases: (1) The error message "update distribute column conflict" is displayed. (2) When the time spent for obtaining a lock exceeds the threshold, an error message is displayed, indicating that the lock times out.
  • Tables with global secondary indexes (GSIs) are not supported. Otherwise, an error is reported.
  • Only Hash distribution is supported. LIST/RANGE distributed tables are not supported. Otherwise, an error is reported.
  • MERGE INTO and UPSERT cannot be used to update distribution keys. Otherwise, an error is reported.
  • GTM_FREE is not supported. Otherwise, an error is reported.
  • Statements with joined tables are not supported. Otherwise, an error is reported.
  • UPDATE+LIMIT is not supported. Otherwise, an error is reported.
• When the UPDATE statement uses a STREAM plan as a subquery, concurrent updates to the same row are not allowed.
• You cannot modify the character set encoding of a database through an UPDATE on a system catalog. This action may lead to exceptions in existing data or some other operations. If you need to modify the encoding, follow the database switching process for data migration.

Syntax

UPDATE [/*+ plan_hint */] [IGNORE] [ ONLY ] {table_name [ partition_clause ] | view_name} [ * ] [ [ AS ] alias ]
SET {column_name = { expression | DEFAULT }
}[, ...]
   [ WHERE condition ]
   [ORDER BY {col_name | expression | position}
      [ASC | DESC][ NULLS { FIRST | LAST } ], ...]
    [ LIMIT { count } ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

Parameters

• plan_hint

  Follows the UPDATE keyword in the /*+ */ format. It is used to optimize the plan of an UPDATE statement block. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.

• IGNORE

  When the UPDATE statement uses the IGNORE keyword, some ERROR-level errors can be degraded to WARNING-level errors, and invalid values can be adjusted to the closest values based on error scenarios. GaussDB supports the following error downgrade scenarios:

  • Violation of the NOT NULL constraint
  • UNIQUE KEY conflict
  • No partition found for the inserted value
  • Unmatch between the inserted data and the specified partition
  • Multiple rows returned for a subquery
  • sql_mode set to loose
• table_name

  Specifies the name (optionally schema-qualified) of the table to be updated. If ONLY is specified before the table name, only the matching rows in that specific table will be updated. If ONLY is not specified, the update will also affect matching rows in other tables inheriting data from the specified table. Currently, the syntax of ONLY with the * option is reserved, but the function is not supported.

  Value range: existing table names.

• partition_clause

  Updates the specified partition.

  PARTITION { ( { partition_name | subpartition_name } [, ...] ) | FOR ( partition_value [, ...] ) }

  For keywords, see SELECT.

  

  If PARTITION specifies multiple partition names, the partition names can be the same. The union set of the partition ranges is used.

• view_name

  Specifies the target view to be updated.

  

  The restrictions on updating views are as follows:

  • The UPDATE operation can be performed only on columns that directly reference user columns in the base table.
  • A view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  • Views that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
  • Views that contain set operations (UNION and EXCEPT) at the top layer are not supported.
  • Views whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
  • Table types supported in views include ordinary tables, temporary tables, global temporary tables, partitioned tables, Ustore tables, and Astore tables.
  • Only one base table can be updated at a time in a multi-table join view.
  • Join views can only update key-preserved tables. For details about the key-preserved table, see CREATE VIEW.
  • System views cannot be updated.
• alias

  Specifies the alias of the target table.

  Value range: character strings complying with Identifier Description.

• table_list

  Specifies a list of table expressions. It is similar to from_list, but you can specify both the target table and joined tables. This parameter is used only in the syntax for updating multiple tables.

• column_name

  Specifies the name of the column to be modified.

  You can reference this column by using the target table alias and column name. Example: UPDATE foo AS f SET f.col_name = 'namecol'

  The column can be referenced in the format of Database name.Alias or Database name.Table name+Column name. Example: UPDATE foo AS f SET public.f.col_name = 'namecol'.

  Value range: existing column names.

• expression

  Specifies a value or expression for a column.

• DEFAULT

  Specifies the default value of a column.

  The value is NULL if there is no default value.

• from_list

  Specifies a list of table expressions. You can use columns of other tables in the WHERE condition. It is similar to specifying a table list in a FROM clause of a SELECT statement.

  

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

• condition

  Specifies an expression that returns a value of type Boolean. Only rows for which this expression returns true are updated. To prevent unexpected outcomes, avoid using numeric types such as int for condition, because these types can be implicitly converted into Boolean values: 0 becomes false, while other values become true.

• ORDER BY

  For keywords, see SELECT.

• LIMIT

  For keywords, see SELECT.

• RETURNING

  Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT.

Example

• Modify all data in the table.

  -- Create the tbl_test1 table and insert data into it.
  m_db=# CREATE TABLE tbl_test1(id int, info varchar(10));
  m_db=# INSERT INTO tbl_test1 VALUES (1, 'A'), (2, 'B');
  
  -- Query.
  m_db=# SELECT * FROM tbl_test1;
   id | info 
  ----+------
    1 | A
    2 | B
  (2 rows)
  
  -- Modify information in the info column in the tbl_test1 table.
  m_db=# UPDATE tbl_test1 SET info = 'aa';
  
  -- Query the tbl_test1 table.
  m_db=# SELECT * FROM tbl_test1;
   id | info 
  ----+------
    1 | aa
    2 | aa
  (2 rows)

• Modify some data in the table.

  -- Modify the data with an id value of 2 in the tbl_test1 table.
  m_db=# UPDATE tbl_test1 SET info = 'bb' WHERE id = 2;
  
  -- Query the tbl_test1 table.
  m_db=# SELECT * FROM tbl_test1;
   id | info 
  ----+------
    1 | aa
    2 | bb
  (2 rows)

• Modify the data and return the modified data.

  -- Modify the data with an id value of 1 in the tbl_test1 table and specify that the info column should be returned.
  m_db=# UPDATE tbl_test1 SET info = 'ABC' WHERE id = 1;
  UPDATE 1
  
  -- Drop the tbl_test1 table.
  m_db=# DROP TABLE tbl_test1;

• Update a view.

  -- Create a schema.
  m_db=# CREATE SCHEMA upd_view;
  CREATE SCHEMA
  m_db=# SET CURRENT_SCHEMA = 'upd_view';
  SET
  
  -- Create a table and insert data into the table.
  m_db=# CREATE TABLE t1 (x1 int, y1 int);
  CREATE TABLE
  m_db=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
  CREATE TABLE
  m_db=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  m_db=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  
  -- Create a single table view.
  m_db=# CREATE VIEW v_upd1 AS SELECT * FROM t1;
  CREATE VIEW
  
  -- Update t1 through a view.
  m_db=# UPDATE v_upd1 SET y1 = 13 where y1 = 3;
  UPDATE 1
  
  -- Create a multi-table view.
  m_db=# CREATE VIEW vv_upd AS SELECT * FROM t1, t2 WHERE x1 = x2;
  CREATE VIEW
  
  -- Update t1 through the join view.
  m_db=# UPDATE vv_upd SET y1 = 1 WHERE y2 = 1;
  UPDATE 1
  
  -- Drop the schema.
  m_db=# DROP SCHEMA upd_view;
  NOTICE:  drop cascades to 4 other objects
  DETAIL:  drop cascades to table t1
  drop cascades to table t2
  drop cascades to view v_upd1
  drop cascades to view vv_upd
  DROP SCHEMA