SQL DELETE Query

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

SQL DELETE Query

Introduction

What is the DELETE Statement?

Syntax for DELETE

Basic Syntax

Example

Step-by-Step Example

1. Create a Sample Table

2. Insert Sample Data

3. Delete a Specific Row

4. Verify the Deletion

Output

5. Delete Multiple Rows

6. Verify the Deletion

Output

7. Delete All Rows

8. Verify the Deletion

Output

Using DELETE Without a WHERE Clause

Example

Warning

Conclusion

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

ChatGPT + Generative AI + Prompt Engineering for Beginners

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

Building Real-Time REST APIs with Spring Boot - Blog App

Building Microservices with Spring Boot and Spring Cloud

Java Full-Stack Developer Course with Spring Boot and React JS

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

Testing Spring Boot Application with JUnit and Mockito

Spring Boot Thymeleaf Real-Time Web Application - Blog App

Master Spring Data JPA with Hibernate

Spring Boot + Apache Kafka Course - The Practical Guide

Java Testing: Mastering JUnit 5 Framework

Reactive Programming in Java: Spring WebFlux and Testing

Spring Boot + RabbitMQ Course - The Practical Guide

Functional Programming in Java (Includes Java Collections)

ChatGPT for Java Developers: Boost Your Productivity with AI

Comments

Post a Comment

Spring Boot 3 Paid Course Published for Free on my Java Guides YouTube Channel

Spring Boot 3 Paid Course Published for Free
on my Java Guides YouTube Channel