Deleting Records (DELETE)

As your database grows, there will be times when you need to remove outdated, incorrect, or unnecessary data. The DELETE statement is used for this purpose — but with great power comes great responsibility!

🎯 What You’ll Learn

• Basic syntax of the DELETE command
• How to delete specific rows using WHERE
• How to delete all rows (with caution)
• Common mistakes to avoid

🔧 Basic Syntax

DELETE FROM table_name WHERE condition ;  

⚠️ WARNING: If you omit the WHERE clause, all records will be deleted!

🧪 Using the students Table Again

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	91.75
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	Advanced SQL	76.00

🛠️ Example 1: Delete a Student by ID

Let’s remove Aman Verma from the table:

DELETE FROM students WHERE id = 5 ;  

✅ Output (after deletion):

id	name
1	Rahul Sharma
2	Anjali Kapoor
3	Vikas Gupta
4	Riya Jain

🛠️ Example 2: Delete All Students in a Specific Course

Suppose we want to remove all students enrolled in "Advanced SQL":

DELETE FROM students WHERE course = 'Advanced SQL' ;  

✅ Output (after deletion):

id	name	course
4	Riya Jain	Python

🛠️ Example 3: Delete All Records (Be Very Careful)

DELETE FROM students;  

This will remove all rows from the table but keep the table structure intact.

🛡️ Tip: Only do this if you're sure or testing in a safe environment!

🧠 Pro Tips

Tip	Why It Matters
Always use WHERE	Prevents accidental full-table deletion
Use SELECT first	Verify which rows will be deleted
Use transactions (if supported)	So you can ROLLBACK if something goes wrong
Create a backup before deletion	Especially for production data

🔍 Real-World Example

Suppose Riya Jain transferred to a different platform and we want to remove her record:

DELETE FROM students WHERE name = 'Riya Jain' ;  

✅ Output:

All records are now deleted if she was the only one left.

⚠️ Common Mistakes

Mistake	Why It's Dangerous
Missing WHERE clause	Deletes everything!
Typos in column names	Causes error or does nothing
Wrong condition	Deletes wrong or no records
Not backing up data	Loss of important information

📌 Summary

• DELETE is used to remove records from a table.
• Always double-check your WHERE condition.
• Deleting is permanent unless using transactions or backups.
• Avoid deleting all records unless absolutely necessary.