SQL DELETE Statement

The SQL DELETE statement can be used to delete records from a database table.

  • Delete statement with a WHERE clause deletes the specific records from a table, that satisfy the WHERE condition.
  • Delete statement without a WHERE clause deletes all the records from a table.

In the previous chapter, we discussed how to insert, select, and update table data.

Now, let's see how to delete the records using the DELETE statement.

Syntax

Here is the basic syntax of a DELETE statement.

  • The table_name represents the table name.
  • The condition represents the condition to filter records on the table for the delete, which can be a combination of multiple conditions combined using logical operators.
DELETE FROM table_name
WHERE condition;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

DELETE Specific Records

The below SQL statement deletes an employee with employee_id 3.

Run this on IDE

DELETE FROM employees
WHERE employee_id = 3;

After successful execution, use the SELECT statement to fetch the records from the table, which doesn't include the deleted record.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

DELETE All Records

The below SQL statement deletes all the records from a table, as the WHERE clause is not included to select specific records.

  • All the records will be deleted.
  • The table will not be deleted and its structure, attributes, and indexes will be intact and untouched.

Run this on IDE

DELETE FROM employees;

After successful execution, use the SELECT statement to fetch the records from the table, which shows no records.

employee_id employee_name gender birth_date hire_date salary

DELETE With IF EXISTS

We may sometimes think if we can use the IF EXISTS keyword on a DELETE statement.

The answer is NO, as the DELETE statement allows the WHERE clause to identify the right record, and deletes it if found.

Tips & Suggestions

Always make sure to use the WHERE clause on the DELETE statement to avoid deleting all the records from a table.

Always be specific while deleting table records.

Overall

We now know how to delete records on a table using the DELETE statement.

Related Links