MySQL DELETE Statement
MySQL DELETE Statement
The DELETE statement in MySQL is used to remove existing records from a table. It is commonly used with the WHERE clause to delete specific rows.
✅ Syntax
-
table_name: The name of the table from which you want to delete records. -
WHERE: Specifies which record(s) should be deleted.
⚠️ Important Warning
If you omit the WHERE clause, all records in the table will be deleted, but the table structure remains intact.
Example:
✔ Deletes all rows in the customers table.
🧠 Example Table: students
| id | name | age | city |
|---|---|---|---|
| 1 | John | 20 | New York |
| 2 | Emma | 21 | Chicago |
| 3 | Raj | 22 | Mumbai |
✅ Example 1: Delete a Single Record
✔ Deletes the record where student id = 1.
✅ Example 2: Delete Multiple Records Based on Condition
✔ Deletes all students whose age is greater than 21.
✅ Example 3: Delete Records with NULL Values
✔ Removes all rows where the city value is NULL.
✅ Reset Auto Increment (Optional)
After many deletes, you may want to reset the auto‐increment counter:
🆚 DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Deletes specific rows | ✔️ Yes | ❌ No |
| Can use WHERE | ✔️ Yes | ❌ No |
| Deletes all rows | ✔️ Yes | ✔️ Yes |
| Faster performance | ❌ No | ✔️ Yes |
| Resets auto-increment | ❌ No | ✔️ Yes |
Example of TRUNCATE:
🎯 Summary
-
Use DELETE to remove data from a table.
-
Always use a WHERE clause unless you want to delete all records.
-
DELETE does not remove the table structure.
