About the DELETE statement

You can use the DELETE statement to delete records from a table. This topic describes how to use the statement.

DELETE statement

Syntax:

DELETE FROM table_name [ WHERE condition ];

Note

If a table contains hundreds of thousands of records, a large transaction is generated, which may reduce the performance. We recommend that you specify the WHERE clause to delete data in batches or use the TRUNCATE TABLE statement.

Examples

Delete data from a table

Delete all rows meeting the o_w_id=2 condition from the ordr table.

DELETE FROM ordr WHERE o_w_id=2;

Delete all rows from the ordr table.

DELETE FROM ordr;

Delete data from a table in batches

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. In the following example, the data meeting the o_w_id < 100000, o_w_id < 200000, and o_w_id < 300000 conditions is deleted in batches.

DELETE FROM ordr WHERE o_w_id < 100000;
DELETE FROM ordr WHERE o_w_id < 200000;
DELETE FROM ordr WHERE o_w_id < 300000;

Clear a table

The TRUNCATE TABLE statement clears a table but retains its structure, including the partitions defined for the table. Logically, this statement is equivalent to the DELETE FROM statement that is used to delete all rows. In the following example, TRUNCATE TABLE is used to clear the ordr table:

obclient> TRUNCATE TABLE ordr;