Deleting Data Using the DELETE Statement

In SQL, the DELETE statement is used to remove one or more rows from a table. It allows you to delete specific records based on specified conditions. Here’s an overview of how to use the DELETE statement to remove data from tables.

Table of Contents

Basic Syntax

The basic syntax of the DELETE statement is as follows:

DELETE FROM table_name
WHERE condition;

table_name: Specifies the name of the table from which data will be deleted.
WHERE condition: Specifies the condition that determines which rows will be deleted.

Deleting All Rows

To delete all rows from a table, you can use the DELETE statement without specifying a condition.

The query bellow deletes all records from the “employees” table, resulting in an empty table.

DELETE FROM employees;

Deleting Specific Rows

To delete specific rows from a table, you need to provide a condition in the WHERE clause that specifies which rows should be deleted.

DELETE FROM employees
WHERE employee_id = 123;

This query deletes the record(s) from the “employees” table where the “employee_id” is 123.

Using Subqueries

You can also use subqueries within the DELETE statement to delete data based on the results of a subquery.

DELETE FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

This query deletes all records from the “employees” table where the “department_id” matches any department ID retrieved from the “departments” table with a location of ‘New York’.

Deleting with JOINs

For more complex scenarios, you can use JOINs in the DELETE statement to delete data based on related tables.

DELETE employees
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE departments.location = 'New York';

This query deletes records from the “employees” table where the employee is associated with a department located in ‘New York’, based on the relationship between the “employees” and “departments” tables.

If your table has defined foreign key constraints, you may need to handle deletion constraints to ensure data integrity. This may involve deleting records from related tables first or updating foreign key references. Be cautious when deleting data with constraints to avoid unintended consequences.

By using the DELETE statement, you can remove data from your tables. Whether you’re deleting all rows, specific rows, using subqueries, or JOINs, the DELETE statement provides a means to manage and remove data from your database. Exercise caution when using DELETE statements, as deleted data cannot be easily recovered, and it permanently removes data from the table.