UPDATE|V4.3.5| docs|Distributed Database

UPDATE

Last Updated：2026-02-03 08:48:37 Updated

Purpose

This statement is used to update data in a table.

Limitations and considerations

Direct value updates on subqueries are not supported for single-table or multi-table updates. Otherwise, an error will be returned. Here is an example:

UPDATE (SELECT * FROM T1) SET C1 = 100;

The result is as follows:

ERROR 1288 (HY000): The target table  of the UPDATE is not updatable

Privilege requirements

To execute the UPDATE statement, the current user must have the UPDATE privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

UPDATE [hint_options] [IGNORE] table_references
    SET update_asgn_list
    [WHERE where_condition_list]
    [ORDER BY order_expression_list]
    [LIMIT row_count];

table_references:
    table_name [PARTITION (partition_name_list)] [, table_name [PARTITION (partition_name_list)] ...]

partition_name_list:
    partition_name [, partition_name ...]

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

where_condition_list:
    where_condition [, where_condition ...]

where_condition:
    expression

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

Parameters

Parameter	Description
hint_options	Optional. Specifies the `hint` option. For more information about hints, see Optimizer Hint.
IGNORE	Optional. If a duplicate key is encountered during an update, the update operation for that row is ignored.
table_references	Specifies the list of tables to update. It can be one or more tables. When updating multiple tables, separate the table names with commas (`,`). For more information, see table_references below.
update_asgn_list	Specifies the columns to update and their corresponding new values.
column_name	The name of the column.
WHERE where_condition_list	Optional. Specifies the list of filter conditions, indicating which rows will be updated. For more information, see where_condition below.
ORDER BY order_expression_list	Optional. Specifies the list of sort keys for the rows to update. It is usually used with the `LIMIT` clause. For more information, see order_expression below.
LIMIT row_count	Optional. Specifies the number of rows to update.

table_references

table_name: Specifies the name of the table whose data needs to be updated. You can also use the PARTITION keyword to specify the partition to update.
partition_name_list: Specifies the list of partition names whose data needs to be updated. It can be one or more partition names. When updating multiple partitions, separate the partition names with commas (,).
- partition_name: Specifies the name of the partition whose data needs to be updated.

Here is an example:

Create a table named tbl1.

CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
  PARTITION BY HASH(col1)
  PARTITIONS 5;

Insert test data into the tbl1 table.

INSERT INTO tbl1 VALUES
  (1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3),
  (4, 'A4', 4),(5, 'A5', 5),(6, 'A6', 6),
  (7, 'A7', 7),(8, 'A8', 8),(9, 'A9', 9);

View the data in the p0 and p1 partitions of the tbl1 table.

SELECT * FROM tbl1 PARTITION(p0, p1);

The result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    5 | A5   |    5 |
|    1 | A1   |    1 |
|    6 | A6   |    6 |
+------+------+------+
3 rows in set

Update the data in the p0 and p1 partitions of the tbl1 table. Concatenate the value of the col2 column with an underscore and the string update, and set the value of the col2 column to the concatenated result.
```
UPDATE tbl1 PARTITION(p0, p1)
  SET col2 = CONCAT(col2, '_', 'update');
```
The result is as follows:
```
Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0
```

View the data in the p0 and p1 partitions of the tbl1 table again.

SELECT * FROM tbl1 PARTITION(p0, p1);

The result is as follows:

+------+-----------+------+
| col1 | col2      | col3 |
+------+-----------+------+
|    5 | A5_update |    5 |
|    1 | A1_update |    1 |
|    6 | A6_update |    6 |
+------+-----------+------+
3 rows in set

where_condition

expression: Specifies the condition expression used to filter the rows to update.

Here is an example:

View the data in the tbl1 table where col1 = 2.

SELECT * FROM tbl1 WHERE col1 = 2;

The result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    2 | A2   |    2 |
+------+------+------+
1 row in set

Update the rows in the tbl1 table where col1 = 2. Set the value of the col2 column to update A2 and the value of the col3 column to 22.
```
UPDATE tbl1
  SET col2 = 'update A2',
      col3 = 22
  WHERE col1 = 2;
```
The result is as follows:
```
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
```

View the data in the tbl1 table where col1 = 2 again.

SELECT * FROM tbl1 WHERE col1 = 2;

The result is as follows:

+------+-----------+------+
| col1 | col2      | col3 |
+------+-----------+------+
|    2 | update A2 |   22 |
+------+-----------+------+
1 row in set

order_expression

expression [ASC | DESC]: Specifies the expression to sort in ascending order (ASC, default) or descending order (DESC).

Here is an example:

View the data in the tbl1 table where the value of the col1 column is greater than 5, and sort the results in descending order based on the value of the col3 column.

SELECT *
  FROM tbl1
  WHERE col1 > 5
  ORDER BY col3 DESC;

The result is as follows:

+------+-----------+------+
| col1 | col2      | col3 |
+------+-----------+------+
|    9 | A9        |    9 |
|    8 | A8        |    8 |
|    7 | A7        |    7 |
|    6 | A6_update |    6 |
+------+-----------+------+
4 rows in set

Update the tbl1 table. Multiply the value of the col3 column by 10 for rows where the value of the col1 column is greater than 5, and sort the results in descending order based on the value of the col3 column. Update the top 2 rows.
```
UPDATE tbl1
  SET col3 = col3*10
  WHERE col1 > 5
  ORDER BY col3 DESC
  LIMIT 2;
```
The result is as follows:
```
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
```

View the data in the tbl1 table again.

SELECT *
  FROM tbl1
  WHERE col1 > 5
  ORDER BY col3 DESC;

The result is as follows:

+------+-----------+------+
| col1 | col2      | col3 |
+------+-----------+------+
|    9 | A9        |   90 |
|    8 | A8        |   80 |
|    7 | A7        |    7 |
|    6 | A6_update |    6 |
+------+-----------+------+
4 rows in set

Examples

Create a sample table named test_tbl1.

CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT);

Insert test data into the test_tbl1 table.

INSERT INTO test_tbl1 VALUES
  (1, 1),(2, 2),(3, 3),
  (4, 4),(5, 5);

Update multiple tables.
1. Modify the value of the col3 column in the tbl1 table and the value of the col2 column in the test_tbl1 table for rows that meet the condition tbl1.col1 = test_tbl1.col1. Set the value of the col3 column in the tbl1 table to 100 and the value of the col2 column in the test_tbl1 table to 200.
```
UPDATE test_tbl1, tbl1
  SET tbl1.col3 = 100,
      test_tbl1.col2 = 200
  WHERE tbl1.col1 = test_tbl1.col1;
```
  The return result is as follows:
```
Query OK, 10 rows affected
Rows matched: 10  Changed: 10  Warnings: 0
```
2. Query the data in the test_tbl1 table.
```
SELECT * FROM test_tbl1;
```
  The return result is as follows:
```
+------+------+
| col1 | col2 |
+------+------+
|    1 |  200 |
|    2 |  200 |
|    3 |  200 |
|    4 |  200 |
|    5 |  200 |
+------+------+
5 rows in set
```
Use the IGNORE keyword to update the data in the test_tbl1 table. If a duplicate key is encountered during the update, the update operation for that row is ignored.
1. Update the test_tbl1 table and add 1 to the col1 value of rows where col1 is greater than 3.
```
UPDATE IGNORE test_tbl1
  SET col1 = col1 + 1
  WHERE col1 > 3;
```
  The return result is as follows:
```
Query OK, 1 row affected
Rows matched: 2  Changed: 1  Warnings: 0
```
2. Query the data in the test_tbl1 table.
```
SELECT * FROM test_tbl1;
```
  The return result is as follows:
```
+------+------+
| col1 | col2 |
+------+------+
|    1 |  200 |
|    2 |  200 |
|    3 |  200 |
|    4 |  200 |
|    6 |  200 |
+------+------+
5 rows in set
```

Update the values in an updatable view.

Create a view named v1.

CREATE VIEW v1 AS SELECT * FROM test_tbl1;

Update the rows in the v1 view that meet the condition v1.col1 = 1 and set the value of the v1.col2 column to 100.
```
UPDATE v1
  SET v1.col2 = 100
  WHERE v1.col1 = 1;
```
The return result is as follows:
```
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
```

Query the data in the v1 view.

SELECT * FROM v1;

The return result is as follows:

+------+------+
| col1 | col2 |
+------+------+
|    1 |  100 |
|    2 |  200 |
|    3 |  200 |
|    4 |  200 |
|    6 |  200 |
+------+------+
5 rows in set

Update the values of the partitioning key.

Create a table named test_tbl2.

obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, PRIMARY KEY(col1, col2))
    PARTITION BY HASH(col2) PARTITIONS 4;

Insert test data into the test_tbl2 table.

obclient> INSERT INTO test_tbl2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);

Update the value of the col2 column in the test_tbl2 table for rows where col1 is 4 and set it to 55.
```
obclient> UPDATE test_tbl2
    SET col2 = 55
    WHERE col1 = 4;
```

References

Update data