MySQL DELETE Statement

The DELETE statement permanently removes rows from a table. It is the D in CRUD — Delete. Once a row is deleted, it is gone — unless you have a backup or are using transactions. This makes DELETE one of the most powerful and most dangerous commands in MySQL, so it must always be used with care.

---

Syntax

DELETE FROM table_name
WHERE condition;

---

Sample Table

All examples on this page use the following students table:

id	name	city	age
1	Rahim Uddin	Dhaka	22
2	Sara Begum	Chittagong	20
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21
5	Rafi Hossain	Rajshahi	24

---

Deleting a Single Row

The safest way to delete one specific row is to target it by its primary key:

DELETE FROM students
WHERE id = 3;

This removes only Karim Ali's row. MySQL confirms:

Query OK, 1 row affected (0.01 sec)

---

Deleting Multiple Rows

If the WHERE condition matches more than one row, all matching rows are deleted at once:

-- Delete all students from Dhaka
DELETE FROM students
WHERE city = 'Dhaka';

This removes both Rahim Uddin (id=1) and Nila Akter (id=4) in a single operation.

---

Verify Before You Delete

Just like with UPDATE, always run a SELECT with the same WHERE condition before deleting, to confirm exactly which rows will be removed:

-- Step 1: Preview what will be deleted
SELECT * FROM students WHERE city = 'Dhaka';

-- Step 2: If the result looks correct, proceed
DELETE FROM students WHERE city = 'Dhaka';

---

Deleting All Rows

To remove every row from a table without dropping the table itself, omit the WHERE clause:

DELETE FROM students;

The table structure (columns, constraints, indexes) remains intact — only the data is removed. The AUTO_INCREMENT counter is not reset by DELETE.

---

DELETE vs TRUNCATE

Both DELETE and TRUNCATE remove rows, but they behave differently:

Feature	DELETE	TRUNCATE
Can use WHERE	✔ Yes	✘ No
Resets AUTO_INCREMENT	✘ No	✔ Yes
Can be rolled back	✔ Yes (in a transaction)	✘ No
Fires DELETE triggers	✔ Yes	✘ No
Speed on large tables	Slower	Much faster

Use DELETE when you need to remove specific rows or want the ability to roll back the operation. Use TRUNCATE when you need to wipe all data quickly and reset the ID counter.

---

DELETE and Foreign Keys

If a row in a parent table is referenced by rows in a child table through a foreign key, MySQL will block the delete by default (the RESTRICT behavior):

-- Will fail if orders exist for customer id=1
DELETE FROM customers WHERE id = 1;
-- ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails

To allow it, either delete the child rows first, or define the foreign key with ON DELETE CASCADE so child rows are removed automatically.

---

Key Points to Remember

• DELETE FROM table WHERE condition — always include WHERE to avoid wiping the whole table.
• Target rows by primary key for precise, single-row deletes.
• Run a SELECT with the same condition first to preview what will be deleted.
• DELETE without WHERE removes all rows but keeps the table structure and does not reset AUTO_INCREMENT.
• Use TRUNCATE when you want to clear all rows quickly and reset the ID counter.
• Foreign key constraints can block deletes — remove child rows first, or use ON DELETE CASCADE.

---

Common Mistakes to Avoid

---

Try It: Delete Inside a Transaction

A second example — wrapping a delete in a transaction so it can be rolled back if something looks wrong before committing:

START TRANSACTION;

DELETE FROM students WHERE age > 30;

-- Check the result looks right, then either:
COMMIT;
-- or, if something's wrong:
-- ROLLBACK;

---

Frequently Asked Questions