SQL DELETE Statement

Summary: in this tutorial, you will learn how to use the SQL DELETE statement to delete one or more rows from a table.

Introduction to SQL DELETE statement #

In SQL, the DELETE statement allows you to delete one or more rows from a table based on a condition.

Here’s the syntax of the DELETE statement:

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

In this syntax:

First, provide the name of the table from which you want to delete rows.
Second, specify a condition in the WHERE clause to identify rows to delete.

The WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows from the table:

DELETE FROM table_name;

In general, the DELETE statement returns the number of row deleted to the client.

Some database systems like PostgreSQL returns the a result set that includes the deleted rows to the client.

If you delete from a table that has a foreign key constraint with the ON DELETE CASCADE option, the DELETE statement will also delete rows from related tables.

SQL DELETE statement examples #

We’ll use the employees and dependents tables to demonstrate the DELETE statement.

Deleting one row from a table #

Suppose David, who has employee id 105, wants to remove Fred from his dependent list.

We know that Fred has the dependent id 16, so we use the following DELETE statement to delete Fred from the dependents table:

DELETE FROM dependents
WHERE dependent_id = 16;Code language: SQL (Structured Query Language) (sql)

Try it

Since the WHERE clause contains the primary key expression that uniquely identifies Fred, the DELETE statement removes one row from the dependents table.

To verify that the row with the dependent id 16 has been deleted, you can query data from the dependents table:

SELECT
  *
FROM
  dependents
WHERE
  dependent_id = 16;Code language: SQL (Structured Query Language) (sql)

Try it

The query returns an empty result set, meaning that the row with the dependent_id 16 was deleted.

Deleting multiple rows from a table #

To delete multiple rows from a table, you use the condition in the WHERE clause to specify the rows to delete.

For example, the following statement uses the IN operator in the WHERE clause of the DELETE statement to delete rows with the dependent id 1, 2, and 3:

DELETE FROM dependents
WHERE dependent_id IN (1, 2, 3);Code language: SQL (Structured Query Language) (sql)

Try it

Deleting all rows from a table #

The following example uses the DELETE statement without an WHERE clause to delete all rows from the dependents table:

DELETE FROM dependents;

Try it

To delete all rows from a table more efficiently, you use the TRUNCATE TABLE statement instead of using the DELETE statement without a WHERE clause.

Summary #

Use the DELETE statement to delete one or more rows from a table.
Use the DELETE statement without a WHERE clause to delete all rows from a table.

Quiz #

Databases #

Was this tutorial helpful ?