MySQL DELETE Query

Introduction

In this chapter, we will learn how to use the DELETE statement in MySQL to remove data from a table. The DELETE statement allows you to delete one or more rows from a table based on specific conditions. We will cover the syntax, examples, and important considerations for using the DELETE statement.

Basic DELETE Query

To delete data from a table, we use the DELETE FROM statement.

Syntax

DELETE FROM table_name WHERE condition;

• table_name: The name of the table from which you want to delete data.
• condition: The condition to specify which rows to delete.

Example

DELETE FROM employees WHERE first_name = 'Rahul' AND last_name = 'Sharma';

This example deletes the row from the employees table where the first_name is 'Rahul' and the last_name is 'Sharma'.

Deleting All Rows

If you omit the WHERE clause, the DELETE statement will delete all rows in the table. Use this with caution.

Example

DELETE FROM employees;

This example deletes all rows from the employees table.

Full Example

Let's go through a full example where we create a table, insert data into it, and perform various DELETE queries.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Basic DELETE Query:

DELETE FROM employees WHERE first_name = 'Rahul' AND last_name = 'Sharma';

6. Query the Data to Verify Deletion:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
2	Priya	Singh	priya.singh@example.com	2023-07-02
3	Amit	Kumar	amit.kumar@example.com	2023-07-03
4	Neha	Verma	neha.verma@example.com	2023-07-04

7. Deleting All Rows:

DELETE FROM employees;

8. Query the Data to Verify Deletion:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date

Important Considerations

• Data Loss: Deleting data is permanent and cannot be undone. Make sure you really want to delete the data before executing the command.
• Conditions: Always use a WHERE clause to specify which rows to delete. Omitting the WHERE clause will delete all rows in the table.
• Constraints: Be aware of constraints such as foreign keys that may affect your ability to delete data. Deleting rows that are referenced by other tables can cause errors.
• Transactions: Use transactions to ensure data integrity when performing multiple related delete operations. This allows you to roll back changes if something goes wrong.

Conclusion

Deleting data from a table is a fundamental operation in MySQL. This chapter covered how to use the DELETE FROM statement to remove data from a table, including examples of deleting specific rows and deleting all rows.