SQL DELETE Statement - Removing Records from Tables

SQL DELETE Statement

Sometimes, cleaning is necessary — not just in classrooms, but in databases too. Maybe a student record was added by mistake, or an old entry needs to be cleared. The DELETE statement helps you do just that — cleanly and precisely.

Syntax of DELETE Statement

DELETE FROM table_name
WHERE condition;

Where

• DELETE FROM: This tells SQL that you want to remove data from a specific table.
• table_name: Replace this with the actual name of the table you want to delete records from.
• WHERE: This keyword is used to specify which rows should be deleted. Without it, all rows in the table will be deleted.
• condition: The condition that identifies the rows to delete. Only rows that meet this condition will be removed.

Without a WHERE clause, all records in the table will be deleted. Use with caution.

Deleting a Specific Record

Let’s say we have a table with a few student entries:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Arjun Deshmukh', '10A', 15, 'Pune'),
(2, 'Priya Reddy', '9B', 14, 'Hyderabad'),
(3, 'Karan Mehta', '10A', 15, 'Surat'),
(4, 'Neha Iyer', '8C', 13, 'Chennai');

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Surat
4	Neha Iyer	8C	13	Chennai
5	Swetha Palvai	9B	15	Hyderabad

Let’s remove the record of student with roll number 4:

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Surat
4	Neha Iyer	8C	13	Chennai
5	Swetha Palvai	9B	15	Hyderabad

SQL Query to remove the record of student with roll number 4:

DELETE FROM students
WHERE roll_no = 4;

students table after DELETE

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Surat
5	Swetha Palvai	9B	15	Hyderabad

DELETE Based on a Condition

Consider that we need to remove all students from class ‘9B’:

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Surat
5	Swetha Palvai	9B	15	Hyderabad

SQL Query to remove all students from class ‘9B’:

DELETE FROM students
WHERE class = '9B';

students table after DELETE

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
3	Karan Mehta	10A	15	Surat

What Happens If You Skip WHERE?

This is where beginners often make a critical mistake. Executing a delete statement without a WHERE clause.

DELETE FROM students;

students table after DELETE

SELECT * FROM students;

All student records would be deleted from the table

roll_no	name	class	age	city

Always double-check your WHERE clause before running DELETE!

DELETE with AND Conditions

Restore the students table using INSERT statements for this example purpose.

Let’s delete a student who is in class ‘10A’ and lives in ‘Surat’:

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
3	Karan Mehta	10A	15	Surat
4	Neha Iyer	8C	13	Chennai
5	Swetha Palvai	9B	15	Hyderabad

SQL Querty to delete a student who is in class ‘10A’ and lives in ‘Surat’:

DELETE FROM students
WHERE class = '10A' AND city = 'Surat';

students table after DELETE

SELECT * FROM students;

roll_no	name	class	age	city
1	Arjun Deshmukh	10A	15	Pune
2	Priya Reddy	9B	14	Hyderabad
4	Neha Iyer	8C	13	Chennai
5	Swetha Palvai	9B	15	Hyderabad

Deleting All Rows – Safely

If you really want to delete everything (e.g., before reimporting fresh data), it’s better to use:

TRUNCATE TABLE students;

TRUNCATE is faster and doesn’t log individual row deletions like DELETE.

Checking Before Deleting

Preview before deleting — always:

SELECT * FROM students WHERE class = '10A';

Once you're sure, then:

DELETE FROM students WHERE class = '10A';

Best Practices

• Never run DELETE without WHERE unless you're absolutely sure.
• Use SELECT first to preview what will be deleted.
• Use PRIMARY KEY or UNIQUE fields when targeting specific rows.

Summary

The DELETE statement helps you maintain a clean and accurate database. Whether you're correcting errors, removing outdated records, or managing data flow — this command ensures your tables stay relevant and precise.