The Ultimate Guide to MySQL DELETE Statement

When it comes to managing data in a relational database management system, the DELETE statement plays a vital role. It is a powerful command that helps remove data from a table in MySQL. In this article, we will explore everything you need to know about the MySQL DELETE statement.

Understanding the MySQL DELETE Statement

The DELETE statement is used to remove data from one or more tables in a database. It is a DML (Data Manipulation Language) statement that affects the rows in the table that satisfies the WHERE clause. The WHERE clause is used to specify the conditions that must be met to delete a row from the table.

The basic syntax of the MySQL DELETE statement is as follows:

DELETE FROM table_name WHERE condition;

Here, table_name is the name of the table that you want to delete data from, and condition specifies the conditions to be met to delete the row. The WHERE clause is optional, but without it, the DELETE statement will remove all rows from the table.

Let’s take an example to understand it better. Consider a table named employees with the following data:

To delete the row where the id is 3, you can use the following DELETE statement:

DELETE FROM employees WHERE id = 3;

After executing the above statement, the employees table will have the following data:

Using MySQL DELETE Statement with Multiple Tables

In some cases, you may need to delete data from multiple tables at once. In such scenarios, you can use the MySQL DELETE JOIN statement. It allows you to remove data from multiple tables that meet the specified condition.

Consider two tables named employees and departments with the following data:

employees table:

departments table:

To delete all the employees from the IT department, you can use the following DELETE JOIN statement:

DELETE employees FROM employees 
JOIN departments ON employees.department_id = departments.id 
WHERE departments.name = "IT";

MySQL DELETE Statement with Triggers

In MySQL, you can use triggers to perform actions automatically when a specific event occurs. It is a powerful feature that can be used along with the DELETE statement.

Consider a table employees with the following data:

Now, suppose you want to track the information every time an employee row is deleted from the employees table. You can create a trigger that inserts the deleted row into a separate table. The trigger can be defined as follows:

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employee_log (id, name, age, gender, salary, department_id, deleted_datetime)
    VALUES (OLD.id, OLD.name, OLD.age, OLD.gender, OLD.salary, OLD.department_id, NOW());
END

Now, every time an employee row is deleted from the employees table, the trigger will fire and insert the deleted row’s data to the deleted_employee_log table.

Conclusion

The MySQL DELETE statement is a powerful command that can help manage data in a relational database management system. It allows you to remove data from one or more tables that satisfy the specified conditions. The DELETE JOIN statement can be used to remove data from multiple tables at once, while triggers can be used to automate activities when a specific event occurs. By mastering the MySQL DELETE statement, you can efficiently manage and manipulate data in your databases.

📕 Related articles about MySQL

• How to use UNIQUE Constraint in SQL
• mysqladmin — A MySQL Server Administration Program
• Installing MySQL on Solaris: A Comprehensive Guide
• MySQL Delete Data: A Comprehensive Guide to Data Deletion in MySQL
• How to use MERGE Statement in SQL
• MySQL RENAME TABLE Statement