Update data|V4.3.3| docs|Distributed Database

Update data

Last Updated：2024-12-02 03:48:29 Updated

After you insert data into a table, you can use the UPDATE statement or other statements to update the records in the table. This topic describes how to use related statements.

Prerequisites

Before you update data in a table, make sure that:

You have connected to a MySQL tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Connection methods.

Note

You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.
You have the UPDATE privilege on the target table. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Update data by using the UPDATE statement

Generally, the UPDATE statement is used to update the data of a table.

The syntax is as follows:

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];

Parameter	Required?	Description
table_name	Yes	The table whose data is to be updated.
column_name = value [, column_name = value]	Yes	The column to be updated. The value after the equal sign (=) is the new value.
[WHERE condition]	No	The condition for updating rows. If no condition is specified, all records corresponding to the column are updated.

Update all records

Increase all values in the value column of the t_insert table by 1. Here is an example:

obclient [test]> UPDATE t_insert SET value = value+1;
Query OK, 4 rows affected
Rows matched: 4  Changed: 4  Warnings: 0

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

When you execute the UPDATE statement, make sure that the transaction is not large. You can use the LIMIT keyword to control the number of records to be updated or the WHERE keyword to control the update scope. When you execute an UPDATE statement that involves more than one hundred thousand records without using the WHERE clause, a large transaction is generated and, the update may fail.

Update part of the records

You can update part of the records in a table. Assume that the t_insert table contains a large number of records. You can use the LIMIT keyword to control the number of records to be updated or the WHERE keyword to control the update scope.

Here is an example:

Use the LIMIT keyword to update the values in the first 100,000 rows of the value column in the t_insert table to value+1.
```
obclient [test]> UPDATE t_insert SET value = value+1 LIMIT 100000;
```
Use the WHERE clause to update all the values equal to 10005 in the value column of the t_insert table to value+1.
```
obclient [test]>  UPDATE t_insert SET value = value+1 where value = 10005;
```

Update the values of the name column in the t_insert table whose id value is 3 to UK.

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> UPDATE t_insert SET name = 'UK' WHERE id = 3;
Query OK, 0 rows affected

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | UK   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

Conflicts caused by updating tables with UNIQUE constraints

When you update data in a table with UNIQUE constraints to identical values, the system returns an error and the date update fails. For example:

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> CREATE UNIQUE INDEX uk_name ON t_insert(name);
Query OK, 0 rows affected

obclient [test]> UPDATE t_insert SET name = 'US' where id = 3;
ERROR 1062 (23000): Duplicate entry 'US' for key 'uk_name'

Update data by using other statements

In addition to the explicit UPDATE statement, you can use other statements to update data. For example, when you use the INSERT statement to insert data, you can use the ON DUPLICATE KEY UPDATE clause to convert the insertion statement into a data update statement to avoid constraint conflicts.

For example, you can use the ON DUPLICATE KEY UPDATE clause to convert a data insertion statement into a data update statement.

  obclient [test]> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
  |  2 | US   | 10002 | 2022-10-12 16:29:16 |
  |  3 | EN   | 10003 | 2022-10-12 16:29:26 |
  |  4 | JP   | 10004 | 2022-10-12 17:02:52 |
  +----+------+-------+---------------------+
  4 rows in set

obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(5, 'CN', 10005) ON DUPLICATE KEY UPDATE name = VALUES(name);
Query OK, 1 row affected

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 16:29:16 |
|  2 | US   | 10002 | 2022-10-12 15:17:17 |
|  3 | UK   | 10003 | 2022-10-12 16:29:26 |
|  4 | JP   | 10004 | 2022-10-12 17:02:52 |
|  5 | CN   | 10005 | 2022-10-12 17:27:46 |
+----+------+-------+---------------------+
5 rows in set

In this example, ON DUPLICATE KEY UPDATE name = VALUES(name) specifies that when the inserted data is duplicate with a value of the primary key, the value of the name column of the conflicting row (3,'EN', 10003) in the original data is updated to the value of the name column of the data to be inserted.