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 and provides examples.
Preparation for updating data
Before you update the data in a table, make sure that:
You have connected to an Oracle-compatible tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Overview of 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 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 4: Update the value of the name column to UK for the row where id = 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;
OBE-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 update data without using the WHERE clause and the number of records reaches hundreds of thousands or millions, a large transaction is generated, which may cause the update to fail.
Example 2: In the t_insert table, update all rows where value is 10003 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
Update data in a remote table by using a DBLink
OceanBase Database supports updating data in OceanBase Database (Oracle-compatible mode) and Oracle Database through a DBLink.
The following example shows how to change the value of the C1 column to 3 in the t2 table in the remote database 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 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.