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

• 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 a 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 that meet the value = 10004 condition 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

If a table contains a small amount of data, you can directly use the DELETE statement to delete all rows.

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: Delete the data that meets the value < 20000, value < 30000, and value < 40000 conditions in sequence.

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 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 empty the t_insert table.

obclient> TRUNCATE TABLE t_insert;

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

Use the DELETE statement to delete data through a DBLink

The current OceanBase Database version allows you to delete data from OceanBase Database in Oracle mode or from an Oracle database through a DBLink.

Here is an example of deleting the row where C2 = 7 from the t5 table in the remote database connected 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

References

• Insert data

• Update data

• Replace data

• Use a DBLink to modify data in a remote database