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 [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

  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)