UPDATE|V4.3.5| docs|Distributed Database

UPDATE

Last Updated：2025-11-14 07:33:32 Updated

Purpose

The statement is used to update data in a table.

Limitations and considerations

You cannot directly update values in a subquery in either a multi-table update statement or a single-table update statement. Otherwise, an error will be returned. Here is an example:

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

The return 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 the update operation, the update operation for the row is ignored.
table_references	Specifies the name of the table to be updated. You can specify one or more tables. Separate the table names with commas. For more information, see table_references.
update_asgn_list	Specifies the columns to be updated and the corresponding new values.
column_name	The name of a column.
WHERE where_condition_list	Optional. Specifies the filter conditions, which specify the rows to be updated. For more information about how to update specific data, see where_condition.
ORDER BY order_expression_list	Optional. Specifies the list of sorting keys for the rows to be updated. This parameter is usually used with the `LIMIT` clause. For more information about the update order, see order_expression.
LIMIT row_count	Optional. Specifies the number of rows to be updated.

table_references

table_name: the name of the table whose data needs to be updated. You can also use the PARTITION keyword to specify a partition whose data needs to be updated.
partition_name_list: a list of partition names whose data needs to be updated. You can specify one or more partition names. Separate the partition names with commas.
- partition_name: the name of a 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 return 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 in the col2 column with underscores and the string update, and update the values in the col2 column to the concatenated results.
```
UPDATE tbl1 PARTITION(p0, p1)
  SET col2 = CONCAT(col2, '_', 'update');
```
The return 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 return 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: the condition expression used to filter the rows to be updated.

Here are some examples:

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

SELECT * FROM tbl1 WHERE col1 = 2;

The return 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 in the col2 column to update A2, and set the value in the col3 column to 22.
```
UPDATE tbl1 
  SET col2 = 'update A2',
      col3 = 22
  WHERE col1 = 2;
```
The return 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 return result is as follows:

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

order_expression

expression [ASC | DESC]: the expression to be sorted in ascending order (ASC, the default value) or descending order (DESC).

Here are some examples:

View the values in the col1 column of the tbl1 table that are greater than 5, and sort the results in descending order based on the values in the col3 column.

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

The return 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 in the col3 column by 10 for rows where col1 is greater than 5, sort the results in descending order based on the values in the col3 column, and update the first two rows.
```
UPDATE tbl1
  SET col3 = col3*10
  WHERE col1 > 5
  ORDER BY col3 DESC
  LIMIT 2;
```
The return result is as follows:
```
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
```

View the data in the tbl1 table.

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

The return 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 data in multiple tables.

Update the col3 column values in the tbl1 table to 100 and the col2 column values in the test_tbl1 table to 200 for rows that meet the condition tbl1.col1 = test_tbl1.col1.
```
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
```

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 data in the test_tbl1 table. If a duplicate key is encountered during the update, the update operation for the row is ignored.
1. Update the test_tbl1 table. Increment the col1 values of rows that meet the condition col1 > 3 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. 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 data in an updatable view.

Create a view named v1.

CREATE VIEW v1 AS SELECT * FROM test_tbl1;

Update the v1.col2 column values in the v1 view to 100 for rows that meet the condition v1.col1 = 1.
```
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

References

Update data