MySQL - Delete Query

MySQL - The DELETE Statement

This tutorial explains how to remove records from a MySQL table using the `DELETE` statement. We will cover deleting single and multiple rows, the critical importance of the `WHERE` clause, and safe deletion practices like using transactions. You will also learn modern, secure methods for executing delete operations from client applications.

Understanding the MySQL DELETE Statement

The DELETE statement is a powerful DML (Data Manipulation Language) command used to remove one or more rows from a table. Because it permanently removes data, it must be used with extreme caution.

Syntax

DELETE FROM table_name
WHERE [condition];

The WHERE clause specifies which rows should be deleted. If you omit the WHERE clause, all rows in the table will be deleted! This is rarely the intended action and can lead to catastrophic data loss.

Best Practices for Safe Deletion

• Always Use a WHERE Clause: Unless you intend to clear an entire table, never run a DELETE statement without a WHERE clause.
• Test with SELECT: Before running a DELETE query, run a SELECT query with the same WHERE clause to verify you are targeting the correct rows. For example: SELECT * FROM table_name WHERE condition;
• Use Transactions: For critical operations, wrap your DELETE statement in a transaction. This allows you to preview the change and roll it back if it’s incorrect. START TRANSACTION; DELETE FROM ...; SELECT * FROM ...; -- Check the result. If wrong, type ROLLBACK;. If correct, type COMMIT;
• Have Backups: Always ensure you have recent, tested backups of your database before performing major data modification tasks.

Prerequisites: Sample Table Setup

Let’s create and populate a CUSTOMERS table for our examples.

CREATE TABLE CUSTOMERS(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(25),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

Deleting Data from a Table

Example: Deleting a Single Row

Let’s delete the customer with ID = 1.

DELETE FROM CUSTOMERS WHERE ID = 1;

Output

Query OK, 1 row affected (0.01 sec)

Verification

A quick SELECT query confirms the row is gone.

SELECT * FROM CUSTOMERS;

The table now starts with ID 2.

Example: Deleting Multiple Rows

You can delete multiple rows by providing a WHERE clause that matches several records. Let’s remove all customers with a salary less than 2000.00.

DELETE FROM CUSTOMERS WHERE SALARY < 2000.00;

Output

Query OK, 1 row affected (0.01 sec) -- Assuming Ramesh was already deleted

Alternatively, you can use the IN operator to delete rows with specific IDs.

DELETE FROM CUSTOMERS WHERE ID IN (6, 7);

Example: Deleting All Rows

To delete all records from a table, you omit the WHERE clause. Use this with extreme caution.

DELETE FROM CUSTOMERS;

A more performant way to clear an entire table is using TRUNCATE TABLE. It’s faster because it doesn’t log individual row deletions and it resets any AUTO_INCREMENT counters. However, TRUNCATE is a DDL command and cannot be easily rolled back within a transaction.

TRUNCATE TABLE CUSTOMERS;

Executing DELETE Statements from a Client Program

It is critical to use prepared statements when executing DELETE queries from an application, especially if the WHERE clause depends on user input. This prevents SQL injection attacks.

Node.js (with mysql2/promise)

require('dotenv').config();
const mysql = require('mysql2/promise');

async function deleteCustomer(customerId) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'your_database'
    });

    // Using a prepared statement with a placeholder (?)
    const [result] = await connection.execute('DELETE FROM CUSTOMERS WHERE ID = ?', [customerId]);

    if (result.affectedRows > 0) {
      console.log(`Successfully deleted customer with ID: ${customerId}`);
    } else {
      console.log(`No customer found with ID: ${customerId}`);
    }

  } catch (error) {
    console.error(`Failed to delete customer: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

// Example usage:
deleteCustomer(5);

Python (with mysql-connector-python)

import mysql.connector
import os

def delete_customer(customer_id):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=os.getenv('DB_NAME', 'your_database')
        )
        cursor = connection.cursor()

        # Prepared statement style for this connector
        delete_query = "DELETE FROM CUSTOMERS WHERE ID = %s"
        cursor.execute(delete_query, (customer_id,))
        connection.commit()

        if cursor.rowcount > 0:
            print(f"Successfully deleted customer with ID: {customer_id}")
        else:
            print(f"No customer found with ID: {customer_id}")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# Example usage:
delete_customer(4)

PHP (with PDO)

<?php
function deleteCustomer(int $customerId): void
{
    $host = $_ENV['DB_HOST'] ?? 'localhost';
    $db   = $_ENV['DB_NAME'] ?? 'your_database';
    $user = $_ENV['DB_USER'] ?? 'root';
    $pass = $_ENV['DB_PASSWORD'] ?? 'password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);

        // Using a prepared statement with a named placeholder
        $stmt = $pdo->prepare('DELETE FROM CUSTOMERS WHERE ID = :id');
        $stmt->execute(['id' => $customerId]);

        if ($stmt->rowCount() > 0) {
            echo "Successfully deleted customer with ID: $customerId\n";
        } else {
            echo "No customer found with ID: $customerId\n";
        }

    } catch (\PDOException $e) {
        // In production, log this error instead of echoing it
        error_log($e->getMessage());
        // You might want to re-throw or handle it differently
        throw new \RuntimeException("Database operation failed.");
    }
}

// Example usage:
deleteCustomer(3);