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 delete data from a table by using statements.

Preparations

Before you delete data from a table, make sure that:

• You have logged on to a MySQL tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Overview.

  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 this table. For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator. For more information, see Modify user privileges.

Use the DELETE statement to delete data

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

Syntax:

DELETE FROM table_name [ WHERE condition ];

Delete part of the data

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

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.

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.

Syntax:

TRUNCATE [TABLE] table_name;

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.

More information

• Insert data

• Update data

• Replace data