UPDATE

Purpose

You can use this statement to modify existing column values in a table.

Syntax

UPDATE [hint_options] dml_table_clause 
    SET update_asgn_list 
    [ WHERE where_condition]
    [{ RETURNING  RETURN } returning_exprs [into_clause]]

dml_table_clause:
    dml_table_name opt_table_alias

update_asgn_list:
    column_name = expr [, expr...]

where_condition:
    expression
    
returning_exprs:
    projection_col_name [,projection_col_name ...]
 
into_clause: 
    { INTO into_var_list  BULK COLLECT INTO into_var_list}

into_var_list:
    { USER_VARIABLE  ref_name } [,{ USER_VARIABLE  ref_name } ...]

Parameters

Notice

A special subquery is similar to a subquery that defines an updatable view. Special subqueries must not contain complex operators (such as GROUP BY, DISTINCT, and WINDOW FUNCTION).

Examples

Create a table named tbl1 and insert test data into the table.

obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+
 COL1  COL2 
+------+------+
    1     1 
    2     2 
    3     3 
    4     4 
    5     5 
+------+------+
5 rows in set

• Single-table update: For the row where tbl1.col1=1 in table tbl1, change the value in column col2 to 100.

  obclient> UPDATE tbl1 SET tbl1.col2 = 100  WHERE tbl1.col1 = 1;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+
   COL1  COL2 
  +------+------+
      1   100 
      2     2 
      3     3 
      4     4 
      5     5 
  +------+------+
  5 rows in set
  

• Single-table update: For the row where v.col1=1, run a subquery and change the value in column col2 to 10.

  obclient> UPDATE (SELECT * FROM tbl1) v SET v.col2 = 10 WHERE v.col1 = 1;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+
   COL1  COL2 
  +------+------+
      1    10 
      2     2 
      3     3 
      4     4 
      5     5 
  +------+------+
  5 rows in set
  

• Single-table update: For the row where tbl1.col1<3 in table tbl1, change the value in column col2 to 100.

  obclient> UPDATE tbl1 SET tbl1.col2 = 100 WHERE tbl1.col1 < 3;
  Query OK, 2 rows affected
  Rows matched: 2  Changed: 2  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+
   COL1  COL2 
  +------+------+
      1   100 
      2   100 
      3     3 
      4     4 
      5     5 
  +------+------+
  5 rows in set (0.00 sec)
  

• Single-table update: Use the RETURNING clause to return the modified data.

  obclient> UPDATE tbl1 SET tbl1.col2 = 1000 WHERE tbl1.col1 = 1 RETURNING col2;
  +------+
   COL2 
  +------+
   1000 
  +------+
  1 row in set
  
  obclient> SELECT * FROM tbl1;
  +------+------+
   COL1  COL2 
  +------+------+
      1  1000 
      2     2 
      3     3 
      4     4 
      5     5 
  +------+------+
  5 rows in set