Db2 DELETE

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:

DELETE FROM table_name
[WHERE condition];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table from which you want to delete data.
• Second, use a condition in the WHERE clause to specify which rows to delete. All rows that cause the condition to 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:

DELETE FROM lists
WHERE list_id = 1;
Code language: SQL (Structured Query Language) (sql)

Here is the message indicating that one row has been deleted:

1 rows affected
Code language: SQL (Structured Query Language) (sql)

You can verify the deletion by using the following SELECT statement:

SELECT
	list_id,
	list_name
FROM
	lists;
Code language: SQL (Structured Query Language) (sql)

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.

DELETE FROM lists
WHERE list_id IN (2,3,4);
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following message indicating that 3 rows have been deleted successfully.

3 rows deleted.
Code language: SQL (Structured Query Language) (sql)

3) Deleting all rows from a table example

The following example uses the DELETE statement to remove all rows from the lists table:

DELETE FROM lists;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

6 rows deleted
Code language: SQL (Structured Query Language) (sql)

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.