Log In

DELETE Query in SQL

DELETE Query in SQL
SQL
29.05.2024
Reading time: 6 min
Hostman Team
Technical writer

The DELETE SQL query is a fundamental command used to remove records from a database table. Proper use of DELETE ensures that unnecessary or outdated data is efficiently removed while maintaining the integrity and performance of the database.

Creating a database and its tables

First, let's create a small database named Company and a few tables to work with: departments, employees, customers, and orders.

-- Create the database Company
CREATE DATABASE Company;
USE Company;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

-- Create the departments table
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

-- Create the employees table
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    department_id INTEGER,
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Create the customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INTEGER,
    employee_id INTEGER,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- Insert data into departments
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Engineering'),
(3, 'HR'),
(4, 'Obsolete');

-- Insert data into employees
INSERT INTO employees (employee_id, first_name, last_name, department_id, hire_date) VALUES
(101, 'John', 'Doe', 1, '2019-06-15'),
(102, 'Jane', 'Smith', 2, '2020-01-20'),
(103, 'Emily', 'Jones', 1, '2018-11-03'),
(104, 'Michael', 'Brown', 4, '2017-05-12');

-- Insert data into customers
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice Johnson'),
(2, 'Bob Davis');

-- Insert data into orders
INSERT INTO orders (order_id, order_date, customer_id, employee_id) VALUES
(1001, '2023-01-10', 1, 101),
(1002, '2023-02-15', 2, 102),
(1003, '2023-03-20', 1, 103);

After creating and populating the tables, the data is as follows:

Departments:

Image1

Employees:

Image3

Customers:

Image2

Orders:

Image5

The examples below always start with these tables with their original data. 

Syntax of the DELETE Query

The DELETE query syntax is straightforward. The basic structure is:

DELETE FROM <table_name>
WHERE <condition>;

  • table_name: Specifies the table from which the records are to be deleted.

  • condition: Defines which records should be erased. If no condition is provided, all records from the table will be deleted (use with caution).

Deleting Data from a Single Table

  • Delete all records from customers

DELETE FROM customers;

Result :

Image4

  • Delete a specific record from employees

DELETE FROM employees WHERE employee_id = 101;

Result:

Image7

  •  Delete based on multiple conditions

DELETE FROM employees WHERE department_id = 2 AND hire_date < '2020-01-01';

Result:

Image6

Deleting Data from Multiple Tables

  •  Deleting using JOIN

DELETE e, d
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Obsolete';

Result:

Image9

Cascading Deletes

Cascading deletes automatically remove related records in other tables. This is typically defined through foreign key constraints.

  • Setting up cascading deletes

ALTER TABLE orders
ADD CONSTRAINT fk_employee
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
ON DELETE CASCADE;

Now, if we delete an employee, their associated orders will also be deleted.

DELETE FROM employees WHERE employee_id = 101;

Result:

Image8

Best Practices for Deleting Data

  • Always Back Up Data: Before performing delete operations, ensure you have a recent backup.

  • Use Transactions: Encapsulate delete operations in transactions to allow rollback in case of errors.

  • Limit Deletes: Use conditions to limit the scope of deletion and avoid deleting unintended data.

  • Log Deletions: Maintain a log of deleted records for auditing and recovery purposes.

  • Test Queries: Test delete queries on a small dataset or development environment before executing on production.

Transaction Management

Transactions ensure that a series of SQL statements are executed as a single unit. If any part of the transaction fails, the entire transaction can be rolled back.

Using Transactions

START TRANSACTION;
DELETE FROM employees WHERE employee_id = 101;
DELETE FROM orders WHERE employee_idd = 101;
COMMIT;

If any delete operation fails, the transaction can be rolled back to maintain data integrity.

Error Handling

Handling errors effectively ensures database integrity and application stability.

Common Errors:

  • Foreign Key Constraint Violations: Occurs when trying to delete a record referenced by another table without cascading deletes.

  • Syntax Errors: Incorrect SQL syntax can cause the DELETE query to fail.

  • Permission Issues: Lack of appropriate permissions can prevent deletions.

Handling Errors in SQL queries

-- Start a transaction
START TRANSACTION;

-- Declare a handler for any errors
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- Rollback the transaction if an error occurs
    ROLLBACK;
    -- Optionally, you can log the error or take other actions here
    SELECT 'An error occurred, transaction rolled back' AS error_message;
END;

-- Perform delete operations
DELETE FROM employees WHERE employee_id = 101;

-- Introduce an error deliberately, e.g., by deleting from a non-existent table
DELETE FROM non_existent_table WHERE id = 1;

-- If no errors occur, commit the transaction
COMMIT;

-- If errors occur, the ROLLBACK in the handler will be executed

When you execute the above script, the transaction will be rolled back due to the deliberate error, and the output will indicate that an error occurred and the transaction was rolled back. This ensures the database remains in a consistent state, and no partial changes are committed. Here is the status of the employees table:

Image3

This approach provides a robust way to handle errors during transactions, ensuring data integrity and allowing you to take appropriate actions when an error occurs.

Conclusion

The DELETE SQL query is a powerful tool for managing data within a database. Proper understanding and careful use of DELETE, along with best practices and transaction management, ensure data integrity and optimal database performance. Always perform deletions with caution and consider the implications of removing data from your database.

Share