`DELETE` Query in DBMS (MySQL) [ English ]

Languages

• English

Introduction

In a Database Management System (DBMS), there are situations where certain records in a table are no longer required. For example, a student may leave an institution, or outdated information may need to be removed from a database.

The DELETE statement is used to remove one or more records from a table. This command allows users to delete specific rows based on a given condition.

Definition

The DELETE query is a Data Manipulation Language (DML) command used to remove existing records (rows) from a table in a database.

Syntax

DELETE FROM table_name
WHERE condition;

Where:

• table_name – The name of the table from which records will be deleted.
• condition – Specifies which records should be removed.

How It Works

When the DELETE command is executed:

1. The DBMS checks the specified table.
2. It identifies the rows that satisfy the WHERE condition.
3. The selected records are removed from the table.
4. The remaining data in the table remains unchanged.

Example Table

Suppose we have a table named students.

Example 1: Deleting a Specific Record

DELETE FROM students
WHERE id = 3;

Result

The record where id = 3 has been removed.

Example 2: Deleting Records Based on a Condition

DELETE FROM students
WHERE course = 'BCA';

This query deletes all students enrolled in the BCA course.

Deleting All Records

If the WHERE clause is omitted, all records from the table will be deleted.

DELETE FROM students;

This removes all rows from the table but the table structure remains unchanged.

Important Notes

• The DELETE command removes specific records from a table.
• Always use the WHERE clause to avoid deleting all records unintentionally.
• The table structure remains intact after deletion.

Key Points

• DELETE is a DML command.
• It is used to remove records from a table.
• The WHERE clause specifies which records will be deleted.
• Without WHERE, all rows will be removed.

Summary

The DELETE command is used to remove unwanted records from a table in a database. It allows users to delete specific rows using conditions or remove all records from a table if necessary. Proper use of the WHERE clause ensures that only the intended data is deleted.