Skip to main content

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 these statements and provides examples.

Prepare for data deletion​

Before you delete data from a table, make sure that the following conditions are met:

  • You have connected to seekdb. For more information, see Overview of connection methods.

  • You have the DELETE privilege on the table. If you want to use the TRUNCATE TABLE statement to delete all data from the table, you also need 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, contact your administrator to grant them. For more information about how to directly grant privileges, see Directly grant privileges.

Delete data by using the DELETE statement​

You can use the DELETE statement to delete some or all data from a table.

info

In addition to the DELETE statement, the REPLACE INTO statement may also delete data. For more information about the REPLACE INTO statement, see Replace data.

The syntax of a simple DELETE statement is as follows:

DELETE FROM table_name [ WHERE condition ] ;
ParameterRequiredDescription
table_nameYesThe name of the table from which you want to delete data.
[ WHERE condition ]NoThe condition clause specifies the conditions that the data to be deleted must meet. If you do not specify this clause, all data in the table is deleted.

Delete some data​

You can use the DELETE statement with the WHERE clause to delete some data from a table.

The following example deletes all rows from the t_insert table where the value column is equal to 10004:

DELETE FROM t_insert WHERE value = 10004;
Query OK, 1 row affected

Delete all data​

If the amount of data in a table is small, you can use the DELETE statement to delete all rows from the table.

Example:

  • Delete all rows from the t_insert table.

    DELETE FROM t_insert;
    Query OK, 3 row affected

    If the table contains more than one million data records, executing a single DELETE statement may cause performance issues. In this case, we recommend that you use the WHERE clause to delete data in batches or directly use the TRUNCATE TABLE statement to delete all data from the table.

  • Filter the t_insert table based on the value column and execute multiple statements to delete data in batches where value < 10000, value < 20000, and value < 30000.

    DELETE FROM t_insert WHERE value < 100000;

    DELETE FROM t_insert WHERE value < 200000;

    DELETE FROM t_insert WHERE value < 300000;

Use the TRUNCATE TABLE statement to delete all data from a table​

The TRUNCATE TABLE statement deletes all data from a specified table but retains the table structure, including partitioning information defined in the table. Logically, this statement is equivalent to a DELETE FROM statement that deletes all rows.

The syntax of the TRUNCATE TABLE statement is as follows:

TRUNCATE [TABLE] table_name;

The following example deletes all data from the t_insert table by using the TRUNCATE TABLE statement:

TRUNCATE TABLE t_insert;

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

References​

Contents

Edit this page