Delete data

After you insert data into a table, you can use the DELETE statement or other statements to delete records from the table. This topic describes how to use related statements and provides examples.

Preparation for deleting data

Before you delete data from 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_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.

• You have the DELETE privilege on the target table. To use the TRUNCATE TABLE statement to clear the data in a table, you must also have the CREATE privilege on the 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.

Use the DELETE statement to delete data

Generally, the DELETE statement is used to delete part of the data or all data from a table.

The syntax of the simple DELETE statement is as follows:

DELETE FROM table_name [ WHERE condition ];

Delete part of the data

You can add a WHERE condition to the DELETE statement to delete the data that meets the condition from a table.

Example 1: Delete all rows whose value is 10004 from 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 | UK   | 10004 | 01-NOV-22  |
+----+------+-------+------------+
3 rows in set

obclient [SYS]> DELETE FROM t_insert WHERE value = 10004;
Query OK, 2 rows affected

obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10002 | 31-OCT-22  |
+----+------+-------+------------+
1 row in set

Delete all data

For a table that contains a small amount of data, you can use the DELETE statement to delete all rows from the table.

Example 2: Delete all records from the t_insert table.

obclient [SYS]> DELETE FROM t_insert;
Query OK, 3 row affected

For a table that contains millions of records, deleting all the records at a time may result in performance issues. We recommend that you delete the data in batches.

Example 3: First delete the data whose value is smaller than 20000; then delete the data whose value is smaller than 30000, and finally delete the data whose value is smaller than 40000.

obclient [SYS]> DELETE FROM t_insert WHERE value < 20000;

obclient [SYS]> DELETE FROM t_insert WHERE value < 30000;

obclient [SYS]> DELETE FROM t_insert WHERE value < 40000;

Use the TRUNCATE TABLE statement to empty a table

The TRUNCATE TABLE statement clears a table but retains its schema, including the partitions defined for the table. Logically, this statement is equivalent to the DELETE FROM statement that is used to delete all rows.

The syntax of the TRUNCATE TABLE statement is as follows:

TRUNCATE [TABLE] table_name;

Example 4: Use the TRUNCATE TABLE statement to clear all data from the t_insert table.

obclient> TRUNCATE TABLE t_insert;

For more information about the usage and description of the TRUNCATE TABLE statement, see TRUNCATE TABLE.

Delete data in a remote table by using a DBLink

OceanBase Database supports deleting data from OceanBase Database (Oracle-compatible mode) and Oracle Database through a DBLink.

The following example shows how to delete the row where C2 = 7 from the t5 table in the remote database through a DBLink:

obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    7 |    7 |
|    8 |    8 |
+------+------+
2 row in set

obclient> DELETE FROM t5@orcl_dblink WHERE C2 = 7;
Query OK, 1 row affected

obclient> commit;
Query OK, 0 rows affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    8 |    8 |
+------+------+
1 row 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.

References

• Insert data

• Update data

• Replace data

• Use a DBLink to modify data in a remote database