Delete Query in MySQL

Introduction

After inserting and updating data, you may sometimes need to remove unwanted records from a table.

In MySQL, this is done using the DELETE query.

The DELETE statement allows you to remove one or more rows from a table.

What is a DELETE Query

The DELETE statement is used to remove existing records from a table.

It does not delete the table itself — only the data inside it.

Basic Syntax

DELETE FROM table_name

WHERE condition;

Example

DELETE FROM Students

WHERE id = 1;

This deletes the student with id = 1.

Delete Multiple Rows

DELETE FROM Students

WHERE age < 18;

This removes all students whose age is less than 18.

Delete All Rows

DELETE FROM Students;

⚠️ This deletes all records from the table.

Note:
Table structure remains intact.

Using LIMIT

DELETE FROM Students

LIMIT 2;

Deletes only 2 rows.

Using ORDER BY with DELETE

DELETE FROM Students

ORDER BY age ASC

LIMIT 1;

Deletes the youngest student.

DELETE vs TRUNCATE

Feature

DELETE

TRUNCATE

Removes rows

Yes

Yes

WHERE allowed

Yes

No

Speed

Slower

Faster

Rollback

Possible

Not possible (usually)

AUTO_INCREMENT reset

No

Yes

Example Scenario

Suppose a student leaves:

DELETE FROM Students

WHERE id = 3;

Important Warning

⚠️ Always use WHERE carefully.

Without WHERE:

  • All records will be deleted
  • Data loss can occur

Common Mistakes

  • Forgetting WHERE clause
  • Deleting wrong records
  • Not backing up important data
  • Using incorrect conditions

Key Points to Remember

  • DELETE removes records from a table
  • Use WHERE to target specific rows
  • Without WHERE, all rows are deleted
  • Table structure remains unchanged
  • Supports LIMIT and ORDER BY