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 the data in a table, make sure that:
You have connected to an Oracle-compatible tenant of OceanBase Cloud.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the mode of the tenant to which you have logged on.You have the
UPDATEprivilege on the target table. If you do not have the required privileges, contact the administrator to obtain the privileges.
Update data by using the UPDATE statement
Generally, the UPDATE statement is used to update the data in a table.
The syntax of 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 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 1: Update the value in the name column to UK for the row where id is 3 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. Here is an 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.
Example 2: Update all the values equal to 10003 in the value column of the t_insert table to value+1.
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 the UPDATE statement to update data through a DBLink
The current OceanBase Cloud version allows you to update data in OceanBase Cloudin the Oracle compatible mode and Oracle Database.
Here is an example of changing the value of the C1 column to 3 for the t2 table in the remote database connected through a DBLink:
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
In the Oracle compatible mode of OceanBase Cloud, you can use the INSERT, DELETE, UPDATE, and MERGE INTO statements to write data from local tables to remote tables.