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.
• You must have the SELECT permission on all tables involved in expression or condition.
• The generated column cannot be directly written. You cannot specify values for generated columns in UPDATE, but you can specify the keyword DEFAULT.
• Currently, the syntax for updating multiple tables cannot be used to update views.
• 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 a single table:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [/*+ plan_hint */] [IGNORE] [ ONLY ] {table_name [ partition_clause ] | subquery | view_name} [ * ] [ [ AS ] alias ]
SET {column_name = { expression | DEFAULT }
}[, ...]
   [ WHERE condition ]
   [ORDER BY {col_name | expression | position}
      [ASC | DESC][ NULLS { FIRST | LAST } ], ...]
    [ LIMIT { count } ];

Update multiple tables:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [/*+ plan_hint */] [IGNORE] table_list
SET {column_name = { expression | DEFAULT }
}[, ...]
    [ WHERE condition ];

Parameters

• WITH [ RECURSIVE ] with_query [, ...]

  Specifies one or more subqueries that can be referenced by name in the main query, which are equivalent to temporary tables. This subquery statement structure is called the common table expression (CTE) structure. When this structure is used, the execution plan contains the CTE SCAN content.

  If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

  The detailed format for with_query is as follows:

  with_query_name [ ( column_name [, ...] ) ] AS ( {select} )

  • with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.
  • column_name specifies the column names displayed in the subquery result set.
  • You can use SELECT in the subqueries.
  • RECURSIVE can appear only after WITH. If there are multiple CTEs, you only need to specify RECURSIVE at the first CTE.
  • When RECURSIVE is used, the data types of the subquery results on both sides of UNION ALL and EXCEPT ALL or UNION [DISTINCT] and EXCEPT [DISTINCT] in the CTE subquery must be converted to the same data type using the CAST function. In addition, the precision and collation of the subquery results on both sides must be the same. For example, WITH RECURSIVE cte (n) AS ( SELECT cast(id as signed int) from table_1 UNION ALL SELECT cast((n + 1) as signed int) FROM cte WHERE n < 5 ) SELECT * FROM cte. For details about the type conversion generated by operators, see Specification Constraints of Logical Operators, Specification Constraints of Bitwise Operators, and Specification Constraints of Arithmetic Operators.
• 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
  

  IGNORE is not supported during upgrade observation.

• 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 [, ...] ) } |

  SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }

  For keywords, see SELECT.

  For examples, see CREATE TABLE SUBPARTITION.

  

  If PARTITION specifies multiple partition names, level-1 and level-2 partition names can coexist and can be the same. The partition range is the union set of the partitions.

• subquery
  Specifies the subquery to be updated. When a subquery is updated, the subquery is regarded as a temporary view.

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  SELECT [/*+ plan_hint */] [ ALL ]
      { * | {expression [ [ AS ] output_name ]} [, ...] }
      [ into_option ]
      [ FROM from_item [, ...] ]
      [ WHERE condition ]
      [ORDER BY {col_name | expression | position} [ ASC | DESC ] [ NULLS { FIRST | LAST } ], ...]
      [ into_option ];
  
  into_option:
  INTO {
    [[[LOCAL | GLOBAL] TEMPORARY] [TABLE] table_name] |
    OUTFILE file_name [CHARACTER SET encoding_name] [FIELDS fields_items] [LINES lines_items] |
    DUMPFILE file_name
  }

  The specified subquery source from_item is as follows:

  {[ ONLY ] {table_name | view_name} [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
  |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |joined_table

  joined_table is as follows:

  joined_table: {
     table_reference [INNER | CROSS] JOIN table_factor [join_specification]
    | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
    | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
  }
  join_specification: {
     ON join_condition
    | USING (join_column_list)
  }
  join_column_list:
      column_name [, column_name] ...

  If a subquery contains only one table, data is updated into the table. If a subquery contains multiple tables or has nested relationships, check whether a key-preserved table exists to determine whether data can be updated. For details about the key-preserved table, see CREATE VIEW.

• view_name

  Specifies the target view to be updated.

  

  The restrictions on updating views and subqueries are as follows:

  • The UPDATE operation can be performed only on columns that directly reference user columns in the base table.
  • A subquery or view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  • Views and subqueries that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
  • Views and subqueries that contain set operations (UNION and EXCEPT) at the top layer are not supported.
  • Views and subqueries 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 and subqueries include ordinary tables, temporary tables, global temporary tables, partitioned tables, level-2 partitioned tables, Ustore tables, and Astore tables.
  • Only one base table can be updated at a time in a multi-table join view or join subquery.
  • Join views or subqueries 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.

  After the GUC compatibility parameter m_format_dev_version is set to 's2':

  • If an alias is specified for table_name, the table can be referenced only by the alias.
  • Only when there are multiple columns in a single table, the expression of each column is calculated from left to right. If a column name is referenced, the updated data is used. The same column name can be modified multiple times.
• 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.

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

  -- Create a schema.
  m_db=# CREATE SCHEMA upd_subqry;
  CREATE SCHEMA
  m_db=# SET CURRENT_SCHEMA = 'upd_subqry';
  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
  
  -- Update t1 through a subquery.
  m_db=# UPDATE (SELECT * FROM t1) SET y1 = 13 where y1 = 3;
  UPDATE 1
  m_db=# UPDATE (SELECT * FROM t1 WHERE y1 < 2) SET y1 = 12 WHERE y1 = 2;
  UPDATE 0
  
  -- Insert a multi-table join subquery.
  m_db=# UPDATE (SELECT * FROM t1, t2 WHERE x1 = x2) SET y1 = 11 WHERE y2 = 1;
  UPDATE 1
  
  -- Drop the schema.
  m_db=# DROP SCHEMA upd_subqry;
  NOTICE:  drop cascades to 2 other objects
  DETAIL:  drop cascades to table t1
  drop cascades to table t2
  DROP SCHEMA

• 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