Summary: in this tutorial, you will learn how to delete one or more rows in a table using the Db2
Introduction to Db2
DELETE statement allows you to delete one or more rows from a table. The following illustrates the syntax of the
DELETE FROM table_name [WHERE condition];
In this syntax:
- First, specify the name of the table from which you want to delete data.
- Second, use a condition in the
WHEREclause to specify which rows to delete. All rows that cause the
conditionto evaluate to true will be deleted.
WHERE clause is optional. If you skip it, the
DELETE statement will remove all rows from the target table.
If no row satisfies the condition, the
DELETE statement just does nothing.
Typically, a table is associated with another table via a relationship: one-to-one, one-to-many, or many-to-many. Depending on the setting of the foreign key constraint such as
ON DELETE CASCADE, the
DELETE statement will automatically delete the rows from the child table when a row from the parent table is deleted.
DELETE statement examples
We will use the
lists table created in the
INSERT statement for the demonstration.
This picture displays the data from the
1) Deleting one row from a table example
DELETE statement removes the row with id 1 from the
DELETE FROM lists WHERE list_id = 1;
Here is the message indicating that one row has been deleted:
1 rows affected
You can verify the deletion by using the following
SELECT list_id, list_name FROM lists;
2) Deleting multiple rows from a table example
The following example uses a condition in the
WHERE clause to delete multiple rows from the
DELETE FROM lists WHERE list_id IN (2,3,4);
Db2 issued the following message indicating that 3 rows have been deleted successfully.
3 rows deleted.
3) Deleting all rows from a table example
The following example uses the
DELETE statement to remove all rows from the
DELETE FROM lists;
The output is as follows:
6 rows deleted
Note that it is more efficient to use the
TRUNCATE TABLE statement to delete all rows from a table, especially a big table.
In this tutorial, you have learned how to use the Db2
DELETE statement to delete one or more rows from a table.