UPDATE|V4.4.2| docs|Distributed Database

UPDATE

Last Updated：2026-03-06 07:02:42 Updated

Purpose

This statement is used to update data in a table.

Limitations and considerations

Directly updating values in subqueries is not supported for both single-table and 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-compatible 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 duplicate keys are encountered during an update, this option will ignore the update for that row.
table_references	Specifies the list of tables to update. You can update 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 filter conditions to determine which rows to update. For more information, see where_condition below.
ORDER BY order_expression_list	Optional. Specifies the sort keys for the rows to update. This option is typically 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 which partitions to update.
partition_name_list: Specifies the list of partitions whose data needs to be updated. You can update one or more partitions. 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 values of the col2 column with an underscore and the string update, and then update the col2 column with 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 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 (ASC, default) or descending (DESC) order.

Here is an example:

View the values of the col1 column in the tbl1 table where the value is greater than 5, and sort the results in descending order by the values 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 values of the col3 column by 10 for rows where the value of the col1 column is greater than 5, sort the results in descending order by the values of the col3 column, and 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 where 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. View 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 table test_tbl1. If a duplicate key error occurs during the update, the update operation for that row is ignored.
1. Update the test_tbl1 table. For rows where the col1 value is greater than 3, increment the col1 value by 1.
```
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. View 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 value in an updatable view.

Create a view named v1.

CREATE VIEW v1 AS SELECT * FROM test_tbl1;

Update the rows in the v1 view where v1.col1 = 1. 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
```

View the data in the v1 table.

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 value 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 = 4 to 55.
```
obclient> UPDATE test_tbl2
    SET col2 = 55
    WHERE col1 = 4;
```

References

Update data