After you insert data into a table, you can use the UPDATE statement to update the records in the table. This topic describes how to use related statements.
Prerequisites
Before you update data, make sure that:
You have logged on to an Oracle tenant of OceanBase Database. For more information, see Connect to OceanBase Database.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant for the mode of the tenant that you have logged on to.You have the
UPDATEprivilege on the target table. For more information about how to view the privileges of the current user, see View user privileges. If you do not have the required privileges, request the administrator to grant the privileges. For more information, see Modify user privileges.
Use the UPDATE statement to update data
Generally, the UPDATE statement is used to update the data of a table.
Syntax:
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 part of the records
You can update part of the records in a table.
Example 4: Update the value of the row whose id is 3 in the name column to UK in the t_insert table.
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10002 | 31-OCT-22 |
| 2 | US | 10004 | 31-OCT-22 |
| 3 | EN | 10004 | 01-NOV-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> UPDATE t_insert SET name = 'UK' WHERE id = 3;
Query OK, 1 rows affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10002 | 31-OCT-22 |
| 2 | US | 10004 | 31-OCT-22 |
| 3 | UK | 10004 | 01-NOV-22 |
+----+------+-------+------------+
3 rows in set
However, if the table has a UNIQUE constraint, the update fails and the system returns an error when the new record is the same as the original one. Error example:
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10002 | 31-OCT-22 |
| 2 | US | 10004 | 31-OCT-22 |
| 3 | UK | 10004 | 01-NOV-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> CREATE UNIQUE INDEX uk_name ON t_insert(name);
Query OK, 0 rows affected
obclient [SYS]> UPDATE t_insert SET name = 'US' where id = 3;
ORA-00001: unique constraint 'US' for key 'UK_NAME' violated
Update all records
Example 1: Increase all values in the value column of the t_insert table by 1.
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 31-OCT-22 |
| 2 | US | 10002 | 31-OCT-22 |
| 3 | EN | 10003 | 01-NOV-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> UPDATE t_insert SET value = value+1;
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10002 | 31-OCT-22 |
| 2 | US | 10003 | 31-OCT-22 |
| 3 | EN | 10004 | 01-NOV-22 |
+----+------+-------+------------+
3 rows in set
When you execute the UPDATE statement, make sure that the transaction is not excessively large. You can use the WHERE keyword to control the 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, which may cause the update to fail. Error returned due to a large transaction:
obclient [SYS]> UPDATE t_insert SET value = value+1;
ERROR 4012 (HY000): Timeout
Example 2: Update all columns whose value values are 10003 to value+1 in the t_insert table.
obclient [SYS]> UPDATE t_insert SET value = value+1 WHERE value = 10003;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Use DBLink to update data
The current OceanBase Database version allows you to use DBLink to update data in OceanBase Database in Oracle mode and Oracle databases.
Example: Use DBLink to update the value of the C1 column in the t2 table in the remote database to 3.
obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected
obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1 | C2 |
+------+------+
| 2 | 2 |
+------+------+
1 row in set
obclient> UPDATE t2@ob_dblink SET C1 = 3;
Query OK, 1 row affected
obclient> commit;
Query OK, 0 rows affected
obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1 | C2 |
+------+------+
| 3 | 2 |
+------+------+
1 rows in set