MySql - Deleting records

Deleting Records in MYSQL

The DELETE statement in MySQL is used to remove existing records from a table. It is a powerful command that permanently deletes data from the database. When used without a WHERE clause, it can remove all records from a table, which makes it critical to use DELETE carefully to avoid unintentional data loss.

This document provides a comprehensive guide to using the DELETE statement in MySQL, including syntax, examples, best practices, common mistakes, performance considerations, and advanced use cases.

1. Basic DELETE Statement Syntax

DELETE FROM table_name
WHERE condition;

The DELETE command requires a WHERE clause to specify which records to remove. Omitting the WHERE clause will delete all rows from the table.

2. Deleting a Single Record

To delete a specific row, use a condition that uniquely identifies it, such as a primary key.

DELETE FROM employees
WHERE employee_id = 101;

3. Deleting Multiple Records

You can delete multiple records that match a specific condition.

DELETE FROM employees
WHERE department_id = 3;

This command removes all employees in department ID 3.

4. DELETE Without WHERE Clause

If you execute DELETE without a WHERE clause, it will remove all records from the table:

DELETE FROM employees;

This command is similar to TRUNCATE but differs in performance and logging.

Difference Between DELETE and TRUNCATE

• DELETE is a DML command and can be rolled back if used within a transaction.
• TRUNCATE is a DDL command and is faster but cannot be rolled back in most storage engines.

5. Using DELETE with JOINs

Delete using INNER JOIN

DELETE e
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'HR';

This command deletes all employees in the HR department using a join condition.

Delete using LEFT JOIN

To delete employees who are not assigned to any department:

DELETE e
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

6. Using DELETE with Subqueries

Delete Based on Subquery Result

DELETE FROM employees
WHERE employee_id IN (
  SELECT employee_id
  FROM salaries
  WHERE net_salary < 3000
);

Note: MySQL may restrict modifying the same table used in the subquery. In that case, use a derived table:

DELETE FROM employees
WHERE employee_id IN (
  SELECT * FROM (
    SELECT employee_id
    FROM salaries
    WHERE net_salary < 3000
  ) AS temp
);

7. DELETE with LIMIT

DELETE can be used with LIMIT to remove a fixed number of records:

DELETE FROM logs
ORDER BY created_at ASC
LIMIT 100;

This is useful for deleting old logs or archiving in batches.

8. DELETE with ORDER BY

You can combine ORDER BY with LIMIT to delete records in a controlled order:

DELETE FROM messages
ORDER BY created_at ASC
LIMIT 10;

9. DELETE with OFFSET (via subquery)

To delete rows with offset, use subquery and LIMIT:

DELETE FROM messages
WHERE message_id IN (
  SELECT message_id
  FROM (
    SELECT message_id
    FROM messages
    ORDER BY created_at ASC
    LIMIT 10 OFFSET 10
  ) AS temp
);

10. DELETE and Transactions

DELETE supports transactions. You can rollback changes if needed.

START TRANSACTION;

DELETE FROM employees
WHERE department_id = 4;

ROLLBACK; -- or COMMIT;

Always wrap DELETE in a transaction when performing critical deletions.

11. Using Prepared DELETE Statements

PREPARE stmt FROM 'DELETE FROM employees WHERE employee_id = ?';
SET @id = 105;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

12. Triggers with DELETE

Triggers can be defined to execute actions before or after DELETE.

AFTER DELETE Trigger

CREATE TABLE deleted_employees (
  employee_id INT,
  deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO deleted_employees (employee_id)
  VALUES (OLD.employee_id);
END;

This trigger logs deleted employees into an archive table.

13. Common Use Cases

• Deleting old records from a log or archive table
• Deleting orphaned records (e.g., child rows with no parent)
• Deleting data in batches using LIMIT
• Deleting test data from development databases

14. Performance Considerations

• DELETE is slower than TRUNCATE for large tables.
• DELETE affects indexes and may cause fragmentation.
• Use DELETE in batches to avoid locking large numbers of rows.
• Always create indexes on columns used in the WHERE clause.

Example: Batching DELETE

-- Repeat this script until no rows are deleted
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL 1 MONTH
LIMIT 1000;

15. DELETE from Multiple Tables (Cascading Delete)

To delete related records from multiple tables, use foreign keys with ON DELETE CASCADE:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
);

Deleting a customer will automatically delete related orders.

16. DELETE vs TRUNCATE vs DROP

17. Safety Tips When Using DELETE

• Always use WHERE clause unless you intend to remove all data.
• Take a backup before running bulk DELETE operations.
• Use SELECT before DELETE to verify rows that match the condition.
• Limit DELETEs in production with a LIMIT clause or within transactions.

Preview rows to delete

SELECT * FROM employees WHERE status = 'inactive';

Then delete

DELETE FROM employees WHERE status = 'inactive';

18. Logging and Auditing DELETEs

Enable binary logs in MySQL for recovery and replication:

[mysqld]
log_bin = mysql-bin

You can also implement a manual audit table using triggers, as shown above.

The DELETE statement in MySQL is a critical tool for managing and maintaining your data. When used properly, it helps maintain data accuracy, control storage, and enforce business rules. However, due to its permanent effect, it must be used with caution. Implement safety measures such as transactions, WHERE clauses, backups, and previews to protect your data integrity. Understanding how DELETE interacts with joins, subqueries, and indexes ensures that your database operations remain efficient and safe.