SQL DELETE Statement

4
0

Introduction

Some records eventually become unnecessary. An application might remove a cancelled registration, clear expired entries, or delete a record created by mistake.

The SQL DELETE statement removes existing rows. Its condition determines which records are targeted, while the table remains available for future use.

What Is DELETE in SQL?

DELETE is a Data Manipulation Language (DML) statement that removes rows from an existing table.

The basic syntax is:

DELETE FROM table_name
WHERE condition;

Unlike UPDATE, which changes column values, DELETE removes the complete matching row. It does not remove individual columns or the table definition.

The examples use a students table with the following structure. This table definition works in both MySQL and PostgreSQL.

CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
department VARCHAR(20),
marks INT
);

student_id

student_name

department

marks

101

Asha

CSE

85

102

Ravi

ECE

72

103

Meera

CSE

91

104

Kabir

ME

60

105

Nila

ECE

NULL

Each example starts from this original data unless stated otherwise.

Deleting a Single Record

Remove Kabir’s record using his identifier:

DELETE FROM students
WHERE student_id = 104;

The remaining table contains Asha, Ravi, Meera, and Nila. Its columns, data types, and constraints remain unchanged.

Using a primary key is useful when the requirement targets one specific record. A condition based on a repeated value, such as a department, may match several rows.

If no row satisfies the condition, nothing is deleted. The statement does not treat the absence of a matching record as a reason to create or change another row.

DELETE : remove one record

DELETE : remove one record

Deleting Multiple Records

A condition can deliberately select several rows:

DELETE FROM students
WHERE marks < 80;

From the original table, Ravi and Kabir qualify. Asha and Meera remain because their marks are at least 80.

Nila also remains, but for a different reason: her missing mark makes marks < 80 unknown. A WHERE condition must be true for the row to qualify.

Other filters work in the same way:

DELETE FROM students
WHERE department IN ('ECE', 'ME');

This targets Ravi, Kabir, and Nila because their known departments match the list.

Combine conditions carefully when the requirement is narrower:

DELETE FROM students
WHERE department = 'ECE'
AND marks < 80;

Only Ravi qualifies.

Deleting Rows with Missing Values

Use IS NULL when deletion depends on missing information:

DELETE FROM students
WHERE marks IS NULL;

This removes Nila’s record from the sample data.

Do not write marks = NULL; ordinary equality does not test whether information is missing.

To remove both low marks and missing marks:

DELETE FROM students
WHERE marks < 80
OR marks IS NULL;

Ravi, Kabir, and Nila qualify.

Deleting an incomplete record and filling its missing value are different requirements. Use UPDATE when the row should remain and only a column value needs correction.

DELETE filter and NULL

DELETE filter and NULL

Deleting All Rows

Omitting WHERE makes every row eligible:

DELETE FROM students;

Assuming the operation succeeds, the table becomes empty. Its definition remains, so new records can still be inserted.

Statement

Scope

Table definition remains?

DELETE ... WHERE ...

Matching rows

Yes

DELETE without WHERE

All rows

Yes

TRUNCATE TABLE

Empties the table

Yes

DROP TABLE

Removes the table and its data

No

DELETE and TRUNCATE can both empty a table, but their transaction, trigger, and foreign key behavior differ.

For ordinary MySQL tables, DELETE can participate in a transaction. TRUNCATE TABLE causes an implicit commit and cannot be undone with an ordinary rollback. Do not assume these rules are identical across database systems.

Deleting a parent record can affect child records that reference it.

Suppose enrollments.student_id references students.student_id. The foreign key’s ON DELETE action determines what happens:

Action

Effect when matching child records exist

RESTRICT

Rejects the parent deletion

NO ACTION

In MySQL , behaves like RESTRICT

CASCADE

Deletes the matching child records

SET NULL

Sets child foreign key values to NULL, where permitted

Deleting a student therefore does not always affect only the students table. A cascade may remove related enrollments, while a restrictive relationship may prevent the deletion.

Check the defined relationship rather than assuming related records are automatically removed.

Checking and Controlling a Deletion

Inspect the intended filter before applying it:

SELECT student_id, student_name, marks
FROM students
WHERE marks < 80;

This previews the current matching records. It does not lock the result against all concurrent changes.

With MySQL , use a transaction when a deletion needs inspection before the final decision:

START TRANSACTION;
DELETE FROM students
WHERE student_id = 104;
SELECT student_id, student_name
FROM students
WHERE student_id = 104;
ROLLBACK;

The record is absent within the transaction after deletion. ROLLBACK restores it because the deletion was not committed.

Use COMMIT instead when retaining the deletion is intended. A later rollback cannot undo an already committed operation, including a successful standalone deletion executed with autocommit enabled.

Common Mistakes

Keep these points clear when removing records:

  • Missing WHERE: All rows become eligible for deletion.

  • Broad conditions: A department or name may identify several records.

  • Confusing rows and columns: DELETE removes rows; ALTER TABLE can remove columns.

  • Incorrect NULL tests: Use IS NULL or IS NOT NULL.

  • Ignoring related tables: Foreign keys can block deletion or propagate it.

  • Expecting automatic recovery: Rollback applies only while the relevant transactional changes remain uncommitted.

Check the intended scope, execute the operation, and verify the resulting data.

Summary

DELETE FROM removes existing rows while retaining the table’s definition. Its WHERE clause can target one record, several records, or records with missing values.

Verify conditions and account for foreign key actions before deleting. Use transactions where appropriate, and distinguish row deletion from emptying or removing the table itself.

CS Core

Read Similar Blogs

Comments0