How to Delete Duplicate Rows in PL/SQL?
Last Updated : 11 Nov, 2024
Inconsistencies and inefficiencies in data management are frequently caused by duplicate rows in a database table. Eliminating duplicate rows is a typical PL/SQL activity to maintain data integrity and improve database performance.
This article will guide you on how to remove duplicated rows in PL/SQL. It provides you with easy-to-understand examples and terminology to help you better understand the procedure.
PL/SQL Deleting Duplicate Rows
In PL/SQL, duplicate rows are removed by finding and eliminating rows with the same values in a subset of columns. Utilizing the ROWID pseudo-column to uniquely identify rows is one method of doing this. The main procedure entails eliminating rows with duplicate ROWIDs after choosing unique rows based on predetermined columns. The following is a summary of the PL/SQL syntax for removing duplicate rows:
Syntax
DELETE FROM table_name WHERE rowid not in (SELECT MIN(rowid) FROM table_name GROUP BY column1, column2, ...);
- table_name: Name of the table containing duplicates.
- MIN(rowid): Ensures one row is retained for each unique combination of values specified in the GROUP BY clause.
Examples of PL/SQL deleting duplicate rows
Example 1: Deleting Duplicate Rows
Consider a scenario where we have a students table with duplicate entries, and we want to remove these duplicates based on the student_id column.
PL/SQL -- Schema for the students table CREATE TABLE students ( student_id NUMBER, student_name VARCHAR2(50), age NUMBER ); -- Insert some sample data with duplicates INSERT INTO students VALUES (1, 'John Doe', 20); INSERT INTO students VALUES (2, 'Jane Smith', 22); INSERT INTO students VALUES (3, 'John Doe', 20); -- Duplicate INSERT INTO students VALUES (4, 'Alice Johnson', 21); INSERT INTO students VALUES (5, 'Jane Smith', 22); -- Duplicate
Output:
student_id | student_name | age |
|---|
1 | John Doe | 20 |
2 | Jane Smith | 22 |
3 | John Doe | 20 |
4 | Alice Johnson | 21 |
5 | Jane Smith | 22 |
-- Delete duplicate rows DELETE FROM students WHERE rowid not in (SELECT MIN(rowid) FROM students GROUP BY student_id);
Output:
student_id | student_name | age |
|---|
1 | John Doe | 20 |
2 | Jane Smith | 22 |
3 | Alice Johnson | 21 |
2 rows deleted.
After executing the above code, duplicate rows based on the student_id column are removed from the students table.
Example 2: Deletion in different Case
Let's now consider a different scenario where we have a sales table with duplicate entries based on multiple columns, and we want to remove these duplicates.
PL/SQL -- Schema for the sales table CREATE TABLE sales ( order_id NUMBER, product_id NUMBER, quantity NUMBER ); -- Insert some sample data with duplicates INSERT INTO sales VALUES (1, 101, 5); INSERT INTO sales VALUES (2, 102, 3); INSERT INTO sales VALUES (3, 101, 5); -- Duplicate INSERT INTO sales VALUES (4, 103, 2); INSERT INTO sales VALUES (5, 102, 3); -- Duplicate
Output:
order_id | product_id | quantity |
|---|
1 | 101 | 5 |
2 | 102 | 3 |
3 | 101 | 5 |
4 | 103 | 2 |
5 | 102 | 3 |
-- Delete duplicate rows DELETE FROM sales WHERE rowid not in (SELECT MIN(rowid) FROM sales GROUP BY order_id, product_id, quantity);
Output:
order_id | product_id | quantity |
|---|
1 | 101 | 5 |
2 | 102 | 3 |
3 | 103 | 2 |
2 rows deleted.
After executing the above code, duplicate rows based on multiple columns (order_id, product_id, quantity) are removed from the sales table.
Conclusion
In PL/SQL, removing duplicate rows is crucial to preserving data integrity and maximising database efficiency. With the help of the examples and syntax offered, users may efficiently find and remove duplicate entries from their database tables. By preventing redundancies and maintaining accuracy, this procedure serves to increase the quality and dependability of the data.
Similar Reads
How to delete duplicate rows in SQLite?
SQLite is an open-source and serverless database system that does not require any server to perform various queries also it is widely used in the development of embedded software like television and mobile phones Sometimes it might happen that we by mistake insert multiple times similar data into ta
3 min read
How to Delete Duplicate Rows in MySQL?
Duplicate rows are a common problem in MySQL databases. Duplicate rows can cause problems with data accuracy and integrity. They can also make it difficult to query and analyze data. Ways to Delete Duplicate Rows in MySQLThere are a few different ways to delete duplicate rows from tables in MySQL: U
4 min read
How to Find Duplicate Rows in PL/SQL
Finding duplicate rows is a widespread requirement when dealing with database analysis tasks. Duplicate rows often create problems in analyzing tasks. Detecting them is very important. PL/SQL is a procedural extension for SQL. We can write custom scripts with the help of PL/SQL and thus identifying
5 min read
How to Delete Duplicate Rows in MariaDB
Duplicate rows in a database can lead to data inconsistencies and inefficiencies. In MariaDB, we can remove duplicate rows using various methods to ensure data integrity and optimize database performance. In this article, We will explore different techniques to identify and delete duplicate rows in
4 min read
SQL Query to Delete Duplicate Rows
Duplicate rows in a database can cause inaccurate results, waste storage space, and slow down queries. Cleaning duplicate records from our database is an essential maintenance task for ensuring data accuracy and performance. Duplicate rows in a SQL table can lead to data inconsistencies and performa
6 min read
Delete Duplicate Rows in MS SQL Server
In MS SQL Server, managing duplicate rows is a common task that can affect the integrity and performance of a database. To address this issue, SQL Server provides several methods for identifying and deleting duplicate rows. In this article, We will explore three effective approaches: using the GROUP
5 min read
How to Delete Duplicate Records in Oracle Database?
Duplicate records in a database can lead to inefficiencies and incorrect query results. Oracle SQL provides several efficient methods for identifying and removing these duplicates, ensuring data accuracy and integrity. This article explains step-by-step how to remove duplicates using the ROWID, a un
3 min read
How to Find Duplicate Records in SQL?
To find duplicate records in SQL, we can use the GROUP BY and HAVING clauses. The GROUP BY clause allows us to group values in a column, and the COUNT function in the HAVING clause shows the count of the values in a group. Using the HAVING clause with a condition of COUNT(*) > 1, we can identify
3 min read
How to Fetch Duplicate Rows in a Table?
Identifying duplicate rows in a database table is a common requirement, especially when dealing with large datasets. Duplicates can arise due to data entry errors, system migrations, or batch processing issues. In this article, we will explain efficient SQL techniques to identify and retrieve duplic
3 min read
How to Remove All Duplicate Rows Except One in SQLite?
SQLite is a lightweight and open-source relational database management system (RDBMS). SQLite does not require any server to process since it is a serverless architecture that can run operations and queries without any server. In this article, we will understand how to remove duplicate rows except o
5 min read