SQL DELETE Statement

The DELETE statement is used to delete data from a table. It can be used in two ways:

1. To delete specific rows from a table
2. To delete all rows from a table

Now let’s take a look at each of these in detail.

Syntax

DELETE FROM table_name WHERE condition;

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

DELETE a Row

To delete a row from a table, you just specify which table to delete the row from, as well as specify which row to delete using the WHERE clause.

Let’s take a simple example. Suppose you wanted to delete the third employee (ID = 3) from the Employees table. The following query will delete it:

DELETE FROM Employees
WHERE ID = 3;

The contents of the ‘Employees’ table after the deletion are:

The DELETE statement always starts with the name of the table from which you want to delete the row. In our case, it’s the ‘Employees’ table. The WHERE clause then tells the DBMS which row to delete. Here it’s the third row.

Note that when you delete a row, you remove the entire row. The DELETE statement does not remove specific columns from the row.

Special care must be taken when using DELELE, because if you accidentally omit the WHERE clause you will end up deleting every row in the table.

DELETE FROM Employees;

The contents of the ‘Employees’ table after the deletion are:

DELETE Multiple Rows

It’s the WHERE clause that determines which rows to delete. To delete multiple rows, use a WHERE clause that selects only the rows you want to delete. For example, the following DELETE statement removes all ‘Developers’ from the Employees table:

DELETE FROM Employees
WHERE Job = 'Developer';

The contents of the ‘Employees’ table after the deletion are:

DELETE All Rows

As you know, omitting the WHERE clause deletes all rows from the table. But if you really want to remove all rows from the table, do not use DELETE. Instead, use the TRUNCATE TABLE statement that performs the same thing faster than DELETE (because data changes are not logged).

Let’s delete all rows from the Employees table using TRUNCATE TABLE statement.

TRUNCATE TABLE Employees;

The contents of the ‘Employees’ table after the deletion are:

Remember! The DELETE statement deletes one or more rows from the table but never deletes the table itself. To delete a table definition as well as the table contents, issue the DROP statement.

DELETE Rows Referenced From Another Table

Sometimes you want to delete rows from one table that are referenced from another table. For example, you have a table called ‘Poor_Performance’, which contains the ID of employees whose performance is poor. The contents of the table ‘Poor_Performance’ are:

Now you want to delete an employee in the ‘Employees’ table if that employee appears in the ‘Poor_Performance’ table. To do this, you can use a subquery in your DELETE statement’s WHERE clause.

DELETE FROM Employees
WHERE ID IN (SELECT ID
             FROM Poor_Performance)

The contents of the ‘Employees’ table after the deletion are: