SQL DELETE

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

Introduction to SQL DELETE statement

To remove one or more rows from a table, you use the DELETE statement. The general syntax for the DELETE statement is as follows:

DELETE
FROM
    table_name
WHERE
    condition;

First, provide the name of the table where you want to remove rows.

Second, specify the condition in the WHERE clause to identify the rows that need to be deleted. If you omit the WHERE clause all rows in the table will be deleted. Therefore, you should always use the DELETE statement with caution.

Generally speaking, the DELETE statement does not return a result set as the SELECT statement. However, it does return the number of rows deleted.

SQL DELETE statement examples

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

SQL DELETE one row in 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 remove Fred from the dependents table.

DELETE FROM dependents 
WHERE
    dependent_id = 16;

Because the WHERE clause contains the primary key expression that identifies Fred, the DELETE statement removes just one row.

You can verify that the row with the dependent id 16 has been deleted by using the following statement:

SELECT 
    COUNT(*)
FROM
    dependents
WHERE
    dependent_id = 16;

SQL DELETE multiple rows example

To delete multiple rows in a table, you use the condition in the WHERE clause to identify the rows that should be deleted. For example, the following statement uses the IN operator to include the dependents of the employees with the id is 100, 101, or 102.

DELETE FROM dependents 
WHERE
    employee_id IN (100 , 101, 102);

SQL DELETE rows from related tables

One employee may have zero or many dependents while one dependent belongs to only one employee. The employee_id column in the dependents table links to the employee_id column in the employees table.

The relationship between the employees and dependents tables is one-to-many.

Logically, a dependent cannot exist without referring to an employee. In other words, when you delete an employee, his or her dependents must be deleted as well.

For example, to remove the employee id 192 and all  the employee’s dependents, you need to execute two DELETE statements as follows:

DELETE
FROM
    employees
WHERE
    employee_id = 192;

DELETE
FROM
    dependents
WHERE
    employee_id = 192;

Most database systems support the foreign key constraint so that when one row from a table is deleted, the rows in the foreign key tables are also removed automatically.

Therefore, when the following DELETE statement is executed:

DELETE
FROM
    employees
WHERE
    employee_id = 192;

All the rows with employee_id 192 are also removed automatically.

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