MySQL DELETE Query: A Complete Guide with Examples Best 2025

The DELETE statement in MySQL is used to remove specific rows from a table. It is commonly used for cleaning up obsolete or temporary data. However, once deleted, data cannot be recovered, so backups are highly recommended before using this command.

---

Table of Contents

1. MySQL DELETE Query: What is the MySQL DELETE Query?
2. Basic Syntax of the DELETE Statement
3. Deleting a Single Row (Example)
4. Deleting Multiple Rows Using IN Clause
5. Deleting All Rows from a Table
6. Precautions When Using DELETE
7. Summary

---

1. What is the MySQL DELETE Query?

The MySQL DELETE query removes specific rows from a database table based on a condition. It is useful for managing database records, such as deleting outdated information or incorrect entries.

⚠ Important: Once a row is deleted, it cannot be recovered, so backing up your database before deletion is strongly advised.

---

2. MySQL DELETE Query Syntax

MySQL DELETE Query: The basic syntax for deleting a row from a MySQL table is:

sql

DELETE FROM table_name WHERE condition;

Breakdown of the Syntax:

• DELETE FROM table_name: Specifies the table from which rows will be removed.
• WHERE condition: Specifies which rows should be deleted. If omitted, all rows will be deleted.

---

3. Deleting a Single Row in MySQL (Example)

MySQL DELETE Query: Let’s say we have a table called movies and we want to delete a specific movie by its movie_id.

Example: Delete a Movie with ID 18

sql

DELETE FROM movies WHERE movie_id = 18;

Before Deletion (movies table):

movie_id	title	director	year_released	category_id
1	Pirates of the Caribbean 4	Rob Marshall	2011	1
18	The Great Dictator	Charlie Chaplin	1920	7
19	Sample Movie	Anonymous	NULL	8

After Deletion (movies table):

The row with movie_id = 18 has been removed.

movie_id	title	director	year_released	category_id
1	Pirates of the Caribbean 4	Rob Marshall	2011	1
19	Sample Movie	Anonymous	NULL	8

---

4. Deleting Multiple Rows Using IN Clause

MySQL DELETE Query: If you need to delete multiple rows at once, you can use the IN clause.

Example: Delete Movies with IDs 19 and 20

sql

DELETE FROM movies WHERE movie_id IN (19, 20);

This will delete both movies in a single query, improving efficiency.

---

5. Deleting All Rows from a Table

MySQL DELETE Query: To remove all records from a table without deleting the table itself, use the DELETE statement without a WHERE clause:

sql

DELETE FROM movies;

OR, use TRUNCATE for better performance:

sql

TRUNCATE TABLE movies;

Difference Between DELETE and TRUNCATE:

• DELETE FROM table_name: Removes rows one by one and logs each deletion (slower).
• TRUNCATE TABLE table_name: Removes all rows instantly without logging (faster).

---

6. Precautions When Using DELETE

1. Always Use WHERE Clause
   
   • If you omit the WHERE clause, all rows in the table will be deleted.

Example of a risky query:
sql

DELETE FROM movies;

•  This will remove all rows in the movies table.

2. Use LIMIT to Control Deletions
   

To avoid accidental mass deletion, limit the number of rows deleted:
sql

DELETE FROM movies WHERE category_id = 8 LIMIT 5;

3. Make Backups Before Deleting
   
   • Since DELETE is permanent, always back up your database before running deletion queries.
4. Use SELECT Before DELETE
   

Run a SELECT query first to verify which rows will be deleted:
sql

SELECT * FROM movies WHERE movie_id IN (19, 20);

• Once confirmed, run the DELETE query.

---

7. Summary

• The MySQL DELETE command removes rows from a table.
• Syntax: DELETE FROM table_name WHERE condition;
• Use the WHERE clause to target specific rows.
• Use the IN clause to delete multiple rows at once.
• Deleting all rows? Use DELETE without WHERE or TRUNCATE TABLE.
• Precautions: Always back up data before running DELETE queries.

By following these best practices, you can safely and efficiently manage data deletion in MySQL.

MYSQL Insert Query

Download My SQL