SQL DELETE

Summary: 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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

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);Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

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.

Now you should understand the SQL DELETE statement and how to apply it to remove one or more rows in a table.