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)
