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 a MySQL 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 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 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.

For example, delete all rows that meet the value = 10004 condition from the t_insert table.

obclient [test]> DELETE FROM t_insert WHERE value = 10004;
Query OK, 1 row affected

Delete all data

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

Here is an example:

• Delete all rows from the t_insert table.

  obclient [test]> 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 by using the WHERE condition, or use the TRUNCATE TABLE statement to empty a table.

• Filter the data in the value column of the t_insert table. Execute multiple statements to respectively delete the data that meets the value < 10000, value < 20000, and value < 30000 conditions in batches.

  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 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 is as follows:

TRUNCATE [TABLE] table_name;

For example, use the TRUNCATE TABLE statement to empty the t_insert table.

obclient [test]> TRUNCATE TABLE t_insert;

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

References

• Insert data

• Update data

• Replace data