SQL DELETE Statement — Removing Records from Tables

A test student record must be removed, but the school still needs the table and every real student’s data.

DELETE removes rows from a table. A WHERE condition limits which records are removed while leaving the table definition in place.

First identify the intended records with SELECT. DELETE applies its row condition to the target table, then removes qualifying rows subject to permissions, constraints and transactional behavior. The result table has fewer rows; it does not replace selected field values with blanks.

Related concepts: Filter with WHERE and Foreign Keys and Transactions.

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.

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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');

The displayed register also includes Swetha. Add this row before the deletion so that the input and output tables match:

INSERT INTO students VALUES (5, 'Swetha Palvai', '9B', 15, 'Hyderabad');

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

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;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

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

DELETE FROM students
WHERE class = '9B';

students table after DELETE

SELECT * FROM students;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

Always double-check your WHERE clause before running DELETE!

SQL · portable concepts; preserved examples identify their engine

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’:

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;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Some engines offer TRUNCATE to empty a table. The following is MySQL syntax. It is a different operation from DELETE and is not automatically the safer choice:

TRUNCATE TABLE students;

In MySQL, TRUNCATE is a table-definition operation that causes an implicit commit, cannot be rolled back like an ordinary transactional DELETE, and does not run DELETE triggers. Foreign-key relationships can prevent it. Other engines have different rules; SQLite does not provide TRUNCATE. Choose according to required behavior, rather than assuming a speed or safety guarantee.

SQL · portable concepts; preserved examples identify their engine

Preview before deleting — always:

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

Once you're sure, then:

DELETE FROM students WHERE class = '10A';

• 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.

DELETE without WHERE can remove all rows. Foreign keys may reject deletion, cascade it or take another configured action. DELETE, TRUNCATE and DROP have different purposes and engine-specific rollback behavior.

Delete the test record with roll number 2 and show the remaining roll numbers.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

DELETE FROM practice_students WHERE roll_no = 2;
SELECT roll_no, name FROM practice_students ORDER BY roll_no;

The row disappears, but the table remains available and the other two records keep their original identities.

DELETE removes rows from a table. A WHERE condition limits which records are removed while leaving the table definition in place.

The row disappears, but the table remains available and the other two records keep their original identities.

Continue your understanding with Filter with WHERE and Foreign Keys and Transactions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.