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.

Prerequisites

Before you delete data, make sure that:

• You have logged on to an Oracle tenant of OceanBase Database. For more information, see Connect to OceanBase Database.

  Note

  You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant for the mode of the tenant that you have logged on to.

• 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 the privileges of the current user, see View user privileges. If you do not have the required privileges, request the administrator to grant the privileges. For more information, see Modify user 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.

Syntax:

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 values are 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 values are smaller than 20000; then delete the data whose value values are smaller than 30000, and finally delete the data whose value values are 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 clear the data from 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.

Syntax:

TRUNCATE [TABLE] table_name;

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

obclient> TRUNCATE TABLE t_insert;

For more information about the TRUNCATE TABLE statement, see TRUNCATE TABLE.

Use DBLink to delete data

The current OceanBase Database version allows you to use DBLink to delete data from OceanBase Database in Oracle mode and Oracle databases.

Example: Use DBLink to delete the rows whose C2 column value is 7 from the t5 table in the remote database.

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

More information

• Insert data

• Update data

• Replace data

• Use a DBLink to modify data in a remote database