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 this statement.
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 in.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 privilege, contact the administrator to obtain the privilege. For more information, see Grant direct 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: 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 clause 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: Increase all the values equal to 10003 in the value column in the t_insert table by 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 Database version allows you to update data in Oracle mode of OceanBase Database and in an 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