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 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_TENANTSview in thesystenant to confirm the mode of the tenant to which you have logged on.You have the
UPDATEprivilege 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 part of the records
You can update part of the records in a table.
For example, 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. 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 columns whose value is 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 a DBLink to update data
The current OceanBase Database version allows you to use a DBLink to update data in OceanBase Database’s Oracle mode and Oracle Database.
The following example show how to change 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 mode of OceanBase Database, you can use the INSERT, DELETE, UPDATE, and MERGE INTO statements to write data from local tables to remote tables. For more information, see Use a DBLink to modify data in a remote database.