Delete Query in SQL

The SQL Delete statement is one of the fundamentals of database management, which allows us to remove unnecessary data precisely while also maintaining the integrity of the data. Instead of dropping the entire table like a DROP statement, it deletes one or more than one row.

In this blog, we will learn about the syntax and implementation of DELETE statements. We will learn to delete a single row, multiple rows, and all rows in a table.

Table of Content

• SQL DELETE Statement
• Examples of SQL DELETE Statement
• Rolling Back DELETE Operations
• COMMIT after DELETE Operations
• Conclusion

SQL DELETE Statement

The SQL Delete statement is used to remove rows from a table. It can be a single row or even multiple rows. We can also pass conditions by using the WHERE clause. Remember that the DELETE statement is a DML (Data Manipulation Language), which states that it just updates the data without changing its structure.

Syntax

DELETE FROM table_name
WHERE some_condition;

Here, we pass a condition that selects the rows that you need to delete from the table. You need to pass the table name after the DELETE statement.

Examples of SQL DELETE Statement

For example, let’s create an IP_employee table. Where we have four columns: employee_id, name, department, and salary.

CREATE TABLE intellipaat_employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO intellipaat_employees (employee_id, name, department, salary) VALUES
(101, 'Alice', 'HR', 50000),
(102, 'Bob', 'Sales', 55000),
(103, 'Charlie', 'IT', 60000),
(104, 'David', 'Sales', 52000),
(105, 'Eve', 'HR', 53000);

SELECT * FROM intellipaat_employees;

1. Delete a Single Row

Let’s delete one row where, we will pass the intellipaat_employees and give the condition, which row you want to delete

For instance, we are going to delete empoyee_id 103.

DELETE FROM intellipaat_employees WHERE employee_id = 103;

Let’s view the updated table.

SELECT * FROM intellipaat_employees;

2. Delete Multiple Records

Now, we will drop multiple rows at once. Let’s remove all rows where the department is Sales.

DELETE FROM intellipaat_employees WHERE department = 'Sales';

Let’s view the updated table.

SELECT * FROM intellipaat_employees;

3. Delete All Rows from the Table

Let’s say I want to remove all the rows from the table. We will simply pass the name of the table.

DELETE FROM intellipaat_employees;

Once you run the above query, the Table will be removed

SELECT * FROM intellipaat_employees;

Rolling Back DELETE Operations

As we know, the DELETE command is a DML operation, which means we can perform a rollback after the execution of a statement. We can use this command when we delete rows by mistake, or we want to repeat the process. Let’s see how we can implement the rollback command.

Begin Transaction

We deleted the employee detail from intellipaat_employees
DELETE FROM intellipaat_employees WHERE employee_id = 103;

Now, let’s check the table after deletion

SELECT * FROM intellipaat_employees;

Since the row has been deleted, let’s do the rollback operation

ROLLBACK;

Suppose you verify the table after rollback. The deleted row will be back.

SELECT * FROM intellipaat_employees;

COMMIT after DELETE Operations

After the final updating of your table, if you want to save the changes, you will use the COMMIT statement. Once you use the COMMIT statement, you won’t be able to perform rollback operations. Let’s see how we can commit the changes.

DELETE FROM intellipaat_employees WHERE employee_id = 103;
SELECT * FROM intellipaat_employees;
COMMIT;

Conclusion

To use the SQL DELETE statement effectively, you need to be careful. Batch operations, indexing, and partitioning can optimize deletions in large datasets. It allows you the flexibility to delete single rows as well as multiple rows. Always back up your data and test your queries first to prevent accidentally losing important information.