SQL DELETE Statement

The DELETE statement is used to remove one or more rows from a table. When a record is no longer needed — such as a student who has left the school — it can be deleted from the database using this statement.

Think of DELETE like removing a page from a notebook — once it is removed, the data on that page is gone. The table structure (columns and other rows) remains intact.

The Reference Table

StudentID	StudentName	Age	City
1	Ravi Sharma	20	Delhi
2	Priya Mehta	22	Mumbai
3	Arjun Nair	19	Chennai
4	Sneha Kapoor	21	Pune
5	Rohit Das	23	Kolkata

Syntax

DELETE FROM table_name
WHERE condition;

Critical: Always use the WHERE clause. Without it, every single row in the table will be deleted.

Deleting a Single Row

DELETE FROM Students
WHERE StudentID = 3;

This removes the row where StudentID equals 3 — which is Arjun Nair's record.

After Delete:

StudentID	StudentName	Age	City
1	Ravi Sharma	20	Delhi
2	Priya Mehta	22	Mumbai
4	Sneha Kapoor	21	Pune
5	Rohit Das	23	Kolkata

The gap in StudentID (jumping from 2 to 4) is normal. AUTO_INCREMENT IDs are not reassigned after a deletion.

Deleting Multiple Rows With a Condition

DELETE FROM Students
WHERE Age > 21;

This deletes all students whose age is greater than 21. Both Priya Mehta (22) and Rohit Das (23) would be removed.

Deleting All Rows (Without WHERE)

-- Warning: This removes ALL rows from the table
DELETE FROM Students;

This empties the entire table but keeps the table structure — the columns still exist, ready to receive new data. This is different from DROP TABLE, which removes the table itself entirely.

DELETE vs TRUNCATE vs DROP

Command	What It Does	Can Use WHERE?	Keeps Table Structure?
`DELETE`	Removes specific rows (or all rows if no WHERE)	Yes	Yes
`TRUNCATE`	Removes all rows quickly, resets AUTO_INCREMENT counter	No	Yes
`DROP TABLE`	Completely removes the table and all its data	No	No

Safe Delete Practice

Before running a DELETE statement, it is a good practice to first run a SELECT with the same WHERE condition to confirm which rows will be removed:

-- Step 1: Check which rows will be deleted
SELECT * FROM Students WHERE StudentID = 3;

-- Step 2: Once confirmed, proceed with the delete
DELETE FROM Students WHERE StudentID = 3;

This two-step approach helps avoid accidental data loss.

Key Points to Remember

Always include the WHERE clause unless the intention is truly to delete all rows.
DELETE is reversible only if wrapped in a transaction (covered in the SQL Transactions topic). Once committed, deleted data cannot be recovered.
Use SELECT with the same condition to preview which rows will be affected before deleting.
DELETE preserves the table structure; DROP TABLE removes the entire table.

Summary

The DELETE statement removes rows from a table based on a condition specified in the WHERE clause. It is one of the most powerful and potentially destructive commands in SQL, so it must be used carefully and always with a specific condition. Always verify the target rows with a SELECT before running the delete.

Previous lessons

Back to courses

Next lessons