After inserting data into a table, you can use the UPDATE statement or other statements to update rows in the table. This topic describes how to use these statements and provides examples.
Preparations for data update
Before updating table data, confirm the following:
Ensure you are connected to the database as a user tenant. For more information about connecting to a database, see Overview of connection methods.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the tenant mode of the current logged-in tenant.Ensure you have the
UPDATEprivilege on the table to be operated on. For more information about checking user privileges, see View user privileges. If you do not have this privilege, contact the administrator to grant the privilege. For more information about granting privileges to users, see Grant privileges directly.
Update data using the UPDATE statement
The UPDATE statement is commonly used to update table data.
The syntax for the UPDATE statement 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 that the rows to be updated must meet. If no condition is specified, all records in the corresponding column of the table are updated. |
Update all records
Update all values in the value column of the t_insert table and increment each value by 1. 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 executing an UPDATE statement, be careful not to create a transaction that is too large. You can control the number of records with the LIMIT keyword or control the scope with the WHERE keyword. This is because when updating data without a condition, if the number of records reaches hundreds of thousands or millions, a large transaction may be created, which could cause execution failure.
Update some records
When updating table data, you can also update only a subset of records. Assuming the t_insert table contains a large number of records, you can control the number of records to update by adding the LIMIT keyword or control the scope by adding the WHERE keyword.
Examples:
Use the
LIMITkeyword to update the values in thevaluecolumn of the first 100,000 rows in thet_inserttable tovalue+1.obclient [test]> UPDATE t_insert SET value = value+1 LIMIT 100000;Use the
WHEREclause to update all values that are equal to10005in thevaluecolumn of thet_inserttable tovalue+1.obclient [test]> UPDATE t_insert SET value = value+1 where value = 10005;Update the value of the
namecolumn toUKin thet_inserttable whereid = 3.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
Update a record with a unique constraint conflict
When a table has a unique constraint, updating to an identical record will cause the system to report an error and the data update will fail. An example error message is shown below.
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 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.
The following example shows how to use the ON DUPLICATE KEY UPDATE clause to convert an insert into an update:
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) means that if the inserted data duplicates a primary key value in the table, the value of the name column in the original conflicting row (3,'EN', 10003) will be updated with the value of the name column in the current data to be inserted.