After inserting 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 these statements and provides examples.
Preparations for data deletion
Before deleting table data, confirm the following:
Ensure you are connected to the database as a user tenant. For more information about connecting to a database, see Overview of connection methods.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the tenant mode of the currently logged-in tenant.Ensure you have the
DELETEprivilege on the target table. If you use theTRUNCATE TABLEstatement to clear table data, you must also have theCREATEprivilege on that table. For more information about checking current user privileges, see View user privileges. If you do not have this privilege, contact the administrator to grant the privilege. For more information about granting privileges to users, see Grant privileges directly.
Delete data using the DELETE statement
The DELETE statement is typically used to delete partial or all data from a table.
Note
In addition to the DELETE statement, you can also use the REPLACE INTO statement to delete data. For related operations and examples of the REPLACE INTO statement, see Replace data.
The syntax for a simple DELETE statement is as follows:
DELETE FROM table_name [ WHERE condition ] ;
Parameter |
Required |
Description |
|---|---|---|
| table_name | Yes | The table from which data is to be deleted. |
| [ WHERE condition ] | No | The condition for deleting data. If no condition is specified, all data in the table is deleted. |
Delete partial data
By adding a WHERE condition to the DELETE statement, you can delete partial data in a table that meets the specified conditions.
Delete all rows in the t_insert table where value = 10004. Example:
obclient [test]> DELETE FROM t_insert WHERE value = 10004;
Query OK, 1 row affected
Delete all data
When the amount of data in a table is small, you can directly use the DELETE statement to delete all rows.
Example:
Delete all row data from the
t_inserttable.obclient [test]> DELETE FROM t_insert; Query OK, 3 row affectedWhen a table contains millions of records, a one-time DELETE operation may encounter performance issues. It is recommended to use the
WHEREcondition to delete data in batches based on the information in the table, or directly use the TRUNCATE TABLE statement to clear the table data.Filter the data in the
valuecolumn of tablet_insertand execute multiple statements to delete data in batches wherevalue < 10000,value < 20000, andvalue < 30000.obclient [test]> DELETE FROM t_insert WHERE value < 100000; obclient [test]> DELETE FROM t_insert WHERE value < 200000; obclient [test]> DELETE FROM t_insert WHERE value < 300000;
Use the TRUNCATE TABLE statement to clear table data
The TRUNCATE TABLE statement is used to completely clear a specified table while retaining the table structure, including the partition information defined in the table. Logically, this statement is equivalent to a DELETE FROM statement that deletes all rows.
The syntax for the TRUNCATE TABLE statement is as follows:
TRUNCATE [TABLE] table_name;
Use the TRUNCATE TABLE statement to clear all data from table t_insert, as shown in the following example:
obclient [test]> TRUNCATE TABLE t_insert;
For more information about the TRUNCATE TABLE statement, see TRUNCATE TABLE.