In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively removing data that is no longer needed or that meets specific criteria.
PL/SQL DELETE Statement
The DELETE statement in the PL/SQL is designed to remove records from a specified table based on the condition defined in the WHERE clause. An operation targets only those rows that satisfy the condition and leaves the rest of the table intact.
If no condition is used, all the rows in the table will be deleted. This statement is the fundamental tool in PL/SQL for managing the lifecycle of the data within the database. The correct usage of the DELETE statement ensures that the database remains clean, well-organized, and consistent.
Syntax:
DELETE FROM table_name
WHERE condition;
Key terms:
- table_name is the name of the table from which we want to delete the records.
- condition specifies which row needs to be deleted. If the condition is not used, all rows in table will deleted.
Employee Table
- In the below code, CREATE TABLE statement is used to create the employeeDetails table with respective columns.
- INSERT INTO statement is used to insert the rows into the employeeDetails table, the values are correspond to the employee_id, name and department columns.
Query:
CREATE TABLE employeeDetails (
employee_id NUMBER(10) PRIMARY KEY,
name VARCHAR2(10),
department VARCHAR2(20)
);
-- Insert a single row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (101, 'John Smith', 'HR');
-- Insert another row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (102, 'Jane Doe', 'IT');
-- Insert a third row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (103, 'Mike Brown', 'Finance');
-- Insert a fourth row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (104, 'Lisa Wong', 'HR');
--Insert a fifth row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (105, 'Alice Grey', 'Marketing');
Output:
employee_id | name | department |
|---|
101 | John Smith | HR |
102 | Jane Doe | IT |
103 | Mike Brown | Finance |
104 | Lisa Wong | HR |
105 | Alice Grey | Marketing |
Department Table
- In the below code, CREATE TABLE statement is used to create the department table with respective columns.
- INSERT INTO statement is used to insert the rows into the department table, the values are corresponded to the department, and name columns.
Query:
CREATE TABLE department (
department VARCHAR2(20),
name VARCHAR2(10)
);
--Insert a single row
INSERT INTO department (department, name)
VALUES ('HR', 'John Smith');
--Insert another row
INSERT INTO department (department, name)
VALUES ('Finance', 'Mike Brown');
--Insert third row
INSERT INTO department (department, name)
VALUES ('IT', 'Jane Doe');
Output:
department | name |
|---|
HR | John Smith |
Finance | Mike Brown |
IT | Jane Doe |
Example 1: Using One Condition in the DELETE Statement
Let us explore the example of using the DELETE statement with the single condition to remove specific rows from employeeDetails table. Suppose we want to delete the record of employee whose department is IT. We can achieve this with the help of DELETE statement with the condition on department column.
Query:
DELETE FROM employeeDetails
WHERE department = 'IT';
Output:
employee_id | name | department |
|---|
101 | John Smith | HR |
103 | Mike Brown | Finance |
104 | Lisa Wong | HR |
105 | Alice Grey | Marketing |
Explanation:
- DELETE FROM employeeDetails: This part of statement is specify that we are delete rows from employeeDetails table.
- WHERE department = 'IT': This condition target only the rows where department is IT. In above employeeDetails table, this condition matches the row for the employee with the employee_id = 102 (Jane Doe).
- Hence, the row for Jane Doe, whose department was IT, is deleted. The rest of the rows in employeeDetails table remain unchanged.
Example 2: Using Two Conditions in the DELETE Statement
In this example, we will see the how to use DELETE statement with the multiple conditions to remove the specific rows from employeeDetails table. Suppose we want to delete the record of employee who works in HR department and has employee_id of 101. This is done with the help of DELETE statement with the two conditions combined with the AND operator.
Query:
DELETE FROM employeeDetails
WHERE department = 'HR' AND employee_id = 101;
Output:
employee_id | name | department |
|---|
102 | Jane Doe | IT |
103 | Mike Brown | Finance |
104 | Lisa Wong | HR |
105 | Alice Grey | Marketing |
Explanation:
- In the above code, DELETE FROM employeeDetails: This part of the statement is specify that deletion is to be performed on employeeDetails table.
- WHERE department = 'HR' AND employee_id = 101: This condition is ensure that only row where department is HR and the employee_id is 101 will deleted from the employeeDetails table.
- The role for John Smith, who is worked in HR department and he had employee_id = 101 is deleted. Other rows, including those are in the HR department but it was in different employee_id values, remains unchanged.
Example 3: Using EXISTS Clause
The EXISTS clause in the SQL (Structured Query Language) is used to check the existence of the rows in the subquery. When the DELETE statement is used with a JOIN or subquery, it allows for the deletion of rows from a table based on the presence of related rows in another table or even within the same table.
This is useful for enforcing relationships between tables or conditions within a single table. Suppose we want to delete the employees from the employeeDetails table who are work in the departments that has no longer exist in department table.
Query:
DELETE FROM employeeDetails e
WHERE EXISTS (
SELECT 1 FROM department d
WHERE d.department = e.department
);
Output:
employee_id | name | department |
|---|
105 | Alice Grey | Marketing |
Explanation:
- In the above code, DELETE FROM employeeDetails e: specifies that the decision is to perform on employeeDetails table with e as its alias.
- WHERE EXISTS: The EXISTS clause is used to check the subquery and return any rows. If the subquery return any rows, then condition is true, and the corresponding rows in the employeeDetails will be deleted.
- Hence, the DELETE statement removes all the employees whose departments exist in department table i.e. HR, IT, Finance. The remaining row is Alice Grey, which is in Marketing department. It does not exist in department table.
Conclusion:
In conclusion, DELETE statement in the PL/SQL is the fundamental tool for the managing the data within the relational database. It allows for the precise removal of the records based on the certain conditions and it as essential part of the database maintenance and the data management.
By the understanding of the DELETE statement effectively, we can ensure that the our database remain accurate, efficient and free of outdated data or unnecessary data.
Similar Reads
SQL DELETE Statement
The SQL DELETE statement is one of the most commonly used commands in SQL (Structured Query Language). It allows you to remove one or more rows from the table depending on the situation. Unlike the DROP statement, which removes the entire table, the DELETE statement removes data (rows) from the tabl
4 min read
MySQL DELETE Statement
In DBMS, CRUD operations (Create, Read, Update, Delete) are essential for effective data management. The Delete operation is crucial for removing data from a database. This guide covers the MySQL DELETE statement, exploring its syntax and providing examples. Understanding how DELETE works helps ensu
6 min read
SQLAlchemy Core - Delete Statement
In this article, we are going to see how to use the DELETE statement in SQLAlchemy against a PostgreSQL database in python. Creating table for demonstration: Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as sh
2 min read
SQL USE Database Statement
SQL(Structured Query Language) is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. It is flexible and user-friendly. In SQL, to interact with the database, the users have to type queries that have certain syntax, a
2 min read
PostgreSQL MERGE Statement
The MERGE statement in PostgreSQL is a powerful data manipulation tool introduced in PostgreSQL 15, enabling conditional INSERT, UPDATE, and DELETE operations in a single command. This feature streamlines data synchronization between tables, making it ideal for tasks such as upserts and handling tab
4 min read
SQL DROP INDEX Statement
In SQL, indexes play an essential role in optimizing query performance by speeding up data retrieval operations. However, indexes can also slow down data modification operations such as INSERT, UPDATE, and DELETE due to the overhead of maintaining them. When an index is no longer required or becomes
5 min read
MySQL DROP TABLE Statement
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides various rich features to create databases and tables, insert data in them, and further manipulate t
6 min read
PL/SQL DELETE JOIN
In database management, removing specific records from a table is a common task especially when cleaning or updating data. One useful technique is the DELETE JOIN which allows us to delete records from one table based on conditions involving another table. In this article, we will explore PL/SQL DEL
4 min read
What is Nested Select Statement in PL/SQL?
PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on. Un
4 min read
PostgreSQL - DELETE USING
The PostgreSQL DELETE USING statement is a powerful and efficient way to delete duplicate rows from a table, especially when duplicates are based on specific conditions. This method is particularly useful in situations where we need to remove rows by comparing data within the same table or across mu
4 min read