Description
You can execute the UPDATE statement to change field values in a table.
Syntax
UPDATE [IGNORE] table_references
SET update_asgn_list
[WHERE where_condition]
[ORDER BY order_list]
[LIMIT row_count];
table_references:
tbl_name [PARTITION (partition_name,...)] [, ...]
update_asgn_list:
column_name = expr [, ...]
order_list:
column_name [ASC|DESC] [, column_name [ASC|DESC]...]
Parameters
| Parameter | Description |
|---|---|
| IGNORE | Ignore the errors that occur when the INSERT statement is being executed. |
| table_references | The name of the table that you want to update. To update multiple tables at a time, separate the table names with commas (,). |
| where_condition | The filter conditions. |
| row_count | The maximum number of rows that can be updated. |
| tbl_name | The name of the table. |
| partition_name | The name of the partition. |
| column_name | The name of the column. |
| column_name ASC | Sort the table by values in a specified column in ascending order and then update the table. |
| column_name DESC | Sort the table by values in a specified column in descending order and then update the table. |
Notes
You cannot execute the UPDATE statement on the output value of a subquery. This rule applies regardless of the number of tables that you want to update. For example, this statement is not allowed: update (select * from t1) set c1 = 100;.
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)
OceanBase(admin@test)>create table t2(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected (0.19 sec)
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
4 rows in set (0.02 sec)
- 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)
- Sort the t1 table by the values in column c2, and change the first two values in column c2 to 100.
OceanBase(admin@test)>update t1 set t1.c2 = 100 order by c2 limit 2;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
- In the p2 partition of the t2 table, find the row that matches the t2.c1 > 2 condition. Then, change the value at the intersection of this row and the c2 column to 100.
OceanBase(admin@test)>update t2 partition(p2) set t2.c2 = 100 where t2.c1 > 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 100 |
+----+------+
4 rows in set (0.06 sec)
- Update multiple tables. In the t1 and t2 tables, find the rows that match the t1.c1 = t2.c1 condition. Change the value at the intersection of the matching row in the t1 table and the c2 column to 100. Change the value at the intersection of the matching row in the t2 table and the c2 column to 200.
OceanBase(admin@test)>update t1,t2 set t1.c2 = 100, t2.c2 = 200 where t1.c2 = t2.c2;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6 Changed: 6 Warnings: 0
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 4 |
+----+------+
4 rows in set (0.00 sec)
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 200 |
| 2 | 200 |
| 3 | 200 |
+----+------+
4 rows in set (0.01 sec)
- Update multiple tables. In the p2 partition in the t1 and t2 tables, find the rows that match the t1.c1 = t2.c1 condition. Change the value at the intersection of the matching row in the t1 table and the c2 column to 100. Change the value at the intersection of the matching row in the t2 table and the c2 column to 200.
OceanBase(admin@test)>update t1,t2 partition(p2) set t1.c2 = 100, t2.c2 = 200 where t1.c2 = t2.c2;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6 Changed: 6 Warnings: 0
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 200 |
| 2 | 200 |
| 3 | 200 |
+----+------+
4 rows in set (0.01 sec)
- Update the values in updatable view v.
OceanBase(admin@test)>create view v as select * from t1;
Query OK, 0 rows affected (0.07 sec)
OceanBase(admin@test)>update 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 v;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)