All Products
Search
Document Center

UPDATE

Last Updated: Jun 18, 2021

Description

You can execute the UPDATE statement to change field 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 [, ...]

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

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

Parameters

Parameter

Description

hint_options

The hint.

dml_table_clause

The name of the table that you want to update. You can specify a base table, an updatable view, or a special subquery.

where_condition

The filter conditions.

update_asgn_list

The columns to update.

returning_exprs

The projection after you update data.

into_clause

After you update the table, insert the projection into the specified table columns.

Notice

A special subquery is similar to a subquery in an updatable view. Such a subquery cannot include complex operators, such as GROUP BY, DISTINCT, or WINDOW FUNCTION.

Examples

Create sample tables t1 and t2.

OceanBase(admin@test)>create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)
  • Update a single table: In the t1 table, find the row that matches the t1.c1 = 1 condition, and change the value at the intersection of this row and the c2 column to 100.

OceanBase(admin@test)>update t1 set t1.c2 = 100 where t1.c1 = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |  100 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.01 sec)
  • Update a single table: Use a subquery to query table v. Find the row that matches the v.c1 = 1 condition in table v, and change the value at the intersection of this row and the c2 column to 100.

OceanBase(admin@test)>update (select * from t1)v set v.c2 = 100 where v.c1 = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |  100 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.01 sec)
  • Update a single table: Execute a statement that includes the RETURNING clause.

OceanBase(admin@test)>update t1 set t1.c2 = 100 where t1.c1 = 1 returning c2;
+------+
| C2   |
+------+
|  100 |
+------+
1 row in set (0.02 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |  100 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.01 sec)