Summary: in this tutorial, you will learn how to delete one or more rows in a table using the Db2 DELETE statement.
Introduction to Db2 DELETE statement
The DELETE statement allows you to delete one or more rows from a table. The following illustrates the syntax of the DELETE statement:
1 2 | 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 theconditionto evaluate to true will be deleted.
The 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.
Db2 DELETE statement examples
We will use the lists table created in the INSERT statement for the demonstration.
This picture displays the data from the lists table:
1) Deleting one row from a table example
The following DELETE statement removes the row with id 1 from the lists table:
1 2 | DELETE FROM lists WHERE list_id = 1; |
Here is the message indicating that one row has been deleted:
1 | 1 rows affected |
You can verify the deletion by using the following SELECT statement:
1 2 3 4 5 | 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 lists table.
1 2 | DELETE FROM lists WHERE list_id IN (2,3,4); |
Db2 issued the following message indicating that 3 rows have been deleted successfully.
1 | 3 rows deleted. |
3) Deleting all rows from a table example
The following example uses the DELETE statement to remove all rows from the lists table:
1 | DELETE FROM lists; |
The output is as follows:
1 | 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.