• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL DELETE

← Previous Next →

In MySQL, the DELETE statement is used to remove one or more rows from a table. Deleting data is an essential operation in database management. As databases grow over time, old, outdated, or incorrect data may need to be removed to maintain accuracy, improve performance, and free up storage.

It is crucial to understand that DELETE affects the actual data in the table. If used incorrectly, it can remove all rows unintentionally, leading to permanent loss of information. Therefore, mastering the DELETE statement, along with safe practices, is fundamental for anyone working with databases.

Basic Syntax

The basic syntax for DELETE is:

DELETE FROM table_name
WHERE condition;

  • table_name specifies the table from which records will be removed.

  • WHERE condition determines which rows are affected.

Example:

DELETE FROM students
WHERE name = 'Meera';

  • This deletes the student whose name is Meera.

  • If the WHERE clause is omitted, all rows in the table will be deleted, so it is important to use it carefully.

Deleting Multiple Rows

DELETE allows removing multiple rows at once if the WHERE clause matches more than one record.

Example:

DELETE FROM students
WHERE age < 18;

  • This command deletes all students younger than 18.

  • Using comparison operators like <, >, =, or BETWEEN helps define which rows to delete.

Deleting All Rows

If your goal is to empty the table but keep its structure (columns, indexes, etc.), you can use:

DELETE FROM students;

  • This removes all data but retains the table structure.

  • For large tables, using TRUNCATE TABLE is faster, but DELETE allows more control, especially with conditions or transaction support.

Using LIMIT with DELETE

MySQL allows the use of LIMIT to delete a specific number of rows, which is helpful for batch deletion:

DELETE FROM students
WHERE city = 'Mumbai'
LIMIT 2;

  • Only the first 2 rows matching the condition are removed.

  • This is particularly useful in large datasets where you want to delete in smaller, controlled batches to avoid accidental mass deletion.

Using ORDER BY with DELETE

You can combine ORDER BY with DELETE to control the deletion order:

DELETE FROM students
ORDER BY age ASC
LIMIT 3;

  • Deletes the three youngest students.

  • ORDER BY ensures you remove specific rows based on a column’s value, giving you precise control in batch operations.

Deleting with Subqueries

DELETE can also remove rows based on conditions from another table using a subquery:

DELETE FROM students
WHERE id IN (
    SELECT student_id FROM alumni
);

  • Deletes students whose IDs exist in the alumni table.

  • Subqueries allow deletion based on related data or criteria not directly present in the main table.

Deleting Using JOINs

In some cases, you need to delete rows by comparing data across multiple tables. MySQL supports DELETE with JOINs:

DELETE s
FROM students s
JOIN alumni a ON s.id = a.student_id;

  • Deletes all students who are listed in the alumni table.

  • This is efficient when removing dependent records in relational databases.

Deleting NULL Values

Sometimes, you may want to delete records with missing or NULL values:

DELETE FROM students
WHERE city IS NULL;

  • Deletes all students whose city is unknown.

  • This helps maintain data integrity by cleaning incomplete records.

Transactions and DELETE

When working with critical tables, it’s best to use transactions with DELETE:

START TRANSACTION;

DELETE FROM students
WHERE age < 18;

ROLLBACK; -- or COMMIT;

  • Transactions allow you to rollback changes if something goes wrong, preventing accidental data loss.

  • Using transactions is especially important when deleting rows across multiple related tables.

Best Practices

  1. Always include WHERE unless you intend to delete all rows.

  2. Test with SELECT first to verify which rows will be affected.

  3. Use LIMIT to control batch deletion in large datasets.

  4. Use transactions for safety on critical tables.

  5. Backup data before performing bulk deletion.

  6. Consider using JOINs or subqueries for conditional deletion across related tables.

  7. Document deletion operations to maintain a record of changes.

Real-Life Example

Suppose the students table contains:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Pune
3 Pooja 18 Pune
4 Riya 21 Delhi
5 Ananya 22 NULL

Example 1 – Delete a single row:

DELETE FROM students
WHERE name = 'Meera';

  • Removes Meera from the table.

Example 2 – Delete multiple rows:

DELETE FROM students
WHERE city = 'Pune';

  • Deletes Pooja and any other students in Pune.

Example 3 – Delete all rows:

DELETE FROM students;

  • Clears the table but keeps its structure intact.

Example 4 – Delete with LIMIT:

DELETE FROM students
WHERE city = 'Mumbai'
LIMIT 1;

  • Deletes only one student from Mumbai, leaving others intact.

Example 5 – Delete using a subquery:

DELETE FROM students
WHERE id IN (SELECT student_id FROM alumni);

  • Deletes students who are already in the alumni table.

Example 6 – Delete NULL values:

DELETE FROM students
WHERE city IS NULL;

  • Removes any student whose city is unknown.

Example 7 – Delete using JOIN:

DELETE s
FROM students s
JOIN alumni a ON s.id = a.student_id;

  • Deletes students listed in the alumni table based on a relationship.

Summary of the Tutorial

  • The DELETE statement removes rows from a table.

  • Always include a WHERE clause to avoid deleting all data unintentionally.

  • You can delete single or multiple rows, and use LIMIT and ORDER BY for controlled deletion.

  • Subqueries and JOINs allow deletion based on related tables.

  • Transactions help safeguard critical deletions.

  • Proper testing, backup, and careful planning are essential when using DELETE.

DELETE is a powerful tool for data management, ensuring databases remain clean, accurate, and up-to-date.

Practice Questions

  1. Delete the student whose name is ‘Meera’ from the students table.

  2. Delete all students younger than 18 years.

  3. Delete students whose city is ‘Pune’.

  4. Delete all rows from the students table without dropping the table.

  5. Delete only one student from ‘Mumbai’ using LIMIT.

  6. Delete students whose city is NULL.

  7. Delete students listed in the alumni table using a subquery.

  8. Delete students by joining the students and alumni tables.

  9. Delete the three youngest students using ORDER BY and LIMIT.

  10. Delete all students whose age is greater than 20 and city is ‘Delhi’.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top