SQL DELETE Query

Introduction

In this chapter, we will learn how to use the DELETE statement in SQL. The DELETE statement is used to remove one or more rows from a table. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the DELETE statement effectively.

What is the DELETE Statement?

The DELETE statement is used to delete existing records in a table. You can delete specific rows based on conditions or all rows in a table. It is important to use the DELETE statement with caution, especially when not specifying a condition, as it can lead to loss of all data in the table.

Syntax for DELETE

Basic Syntax

DELETE FROM table_name
WHERE condition;

• table_name: The name of the table from which you want to delete rows.
• condition: The condition that must be met for a row to be deleted. If omitted, all rows in the table will be deleted.

Example

Assume we have a table named employees:

DELETE FROM employees
WHERE id = 1;

This command deletes the row from the employees table where the id is 1.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1),
('Sita', 'Patel', 'sita.patel@example.com', 2),
('Arjun', 'Singh', 'arjun.singh@example.com', 1),
('Priya', 'Sharma', 'priya.sharma@example.com', 2),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3);

3. Delete a Specific Row

To delete the row where the id is 1:

DELETE FROM employees
WHERE id = 1;

4. Verify the Deletion

SELECT * FROM employees;

Output

id	first_name	last_name	email	department_id
2	Sita	Patel	sita.patel@example.com	2
3	Arjun	Singh	arjun.singh@example.com	1
4	Priya	Sharma	priya.sharma@example.com	2
5	Ramesh	Kumar	ramesh.kumar2@example.com	3

5. Delete Multiple Rows

To delete all employees in department 2:

DELETE FROM employees
WHERE department_id = 2;

6. Verify the Deletion

SELECT * FROM employees;

Output

id	first_name	last_name	email	department_id
3	Arjun	Singh	arjun.singh@example.com	1
5	Ramesh	Kumar	ramesh.kumar2@example.com	3

7. Delete All Rows

To delete all rows from the employees table:

DELETE FROM employees;

8. Verify the Deletion

SELECT * FROM employees;

Output

The table will be empty:

id	first_name	last_name	email	department_id

Using DELETE Without a WHERE Clause

Using the DELETE statement without a WHERE clause will delete all rows in the table. This is equivalent to truncating the table but can be slower because it logs individual row deletions.

Example

To delete all rows from the employees table:

DELETE FROM employees;

Warning

Be very careful when using DELETE without a WHERE clause, as it will remove all data from the table.

Conclusion

The DELETE statement is used for removing records from a table. This chapter covered the basic syntax, deleting specific rows, deleting multiple rows, deleting all rows, and using DELETE with JOIN. Understanding how to use the DELETE statement effectively will enhance your ability to manage and manipulate your database data.