SQL DELETE Statement

The SQL DELETE statement is used to remove one or more records from a table. Unlike the UPDATE statement, which modifies existing records, the DELETE statement permanently removes rows based on a specified condition.

In this tutorial, we will explain the syntax, usage, and various examples of the DELETE statement.

Syntax of SQL DELETE Statement

The basic syntax of an SQL DELETE statement is as follows:

</>
Copy
DELETE FROM table_name
WHERE condition;

Each part of this syntax has a specific purpose:

  • DELETE FROM: Specifies the table from which you want to delete data.
  • table_name: The name of the table where records will be deleted.
  • WHERE: Filters the rows to be deleted. Without a WHERE clause, all rows in the table will be deleted, which should be used with caution.

For safety, it’s best to always use a WHERE clause with DELETE to avoid accidental deletion of all records in the table.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the DELETE statement in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Deleting Records in the Table

Now, let’s explore different scenarios of deleting records in this table.