How to Find Duplicate Rows in PL/SQL

Last Updated : 06 Nov, 2024

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 duplicate data eventually becomes very easy.

PL/ SQL also provides us with various other methods to easily solve our duplicate data problem. In this article, we will explore multiple methods to identify duplicate rows in PL/SQL, including a basic iterative approach and using the powerful GROUP BY clause. Each method will be demonstrated with examples and explanations, providing you with the knowledge to efficiently locate and manage duplicate data in your PL/SQL environment.

Methods to Find Duplicate Rows in PL/SQL

We need to implement the COUNT() function, to find the duplicate rows in the table. We have to count several rows that appear more than once in a table. We have to keep the count of each row of the table. If any row appears to have a count of more than 1, then it will be considered a duplicate row. Some common approaches are as follows:

Table of Content

Demo SQL Database

In this a on "finding duplicate rows in PL/SQL", we will use the following table for examples.

Create Table:

PL/SQL

CREATE TABLE geeksforgeeks  (      id NUMBER ,      name VARCHAR2(50),      rank NUMBER  ); INSERT INTO geeksforgeeks (id, name, rank) VALUES (108, 'Vishu', 135);  INSERT INTO geeksforgeeks (id, name, rank) VALUES (109, 'Ayush', 136);  INSERT INTO geeksforgeeks (id, name, rank) VALUES (110, 'Sumit', 137);  INSERT INTO geeksforgeeks (id, name, rank) VALUES (109, 'Ayush', 136);  INSERT INTO geeksforgeeks (id, name, rank) VALUES (108, 'Vishu', 135);

After running the above commands, the geeksforgeeks table will contain duplicate rows, which we will attempt to identify using PL/SQL.

Method 1: Basic Approach Using COUNT()

In this method, we will use most common method to find the duplicate values from the table. In short, we will use the most basic approach.

We will create some unique combinations and iterate through each combination. If we find that any combination has more than 1 as count, then we will consider this a duplicate.

Query:

DECLARE     --varibale declaration     dupli_count INTEGER; BEGIN     --we are iterating over distinct rows only.     FOR i IN (SELECT distinct id, name, rank FROM geeksforgeeks) LOOP         --if the count has value more than 1, then we will consider it as duplicate          SELECT COUNT(*) INTO dupli_count         FROM geeksforgeeks         WHERE id = i.id AND name = i.name AND rank = i.rank;          IF dupli_count > 1 THEN             DBMS_OUTPUT.PUT_LINE('duplicate rows ID: ' || i.id ||  ' and name: ' || i.name || ' and rank: ' || i.rank || ' having count: ' || dupli_count);         END IF;     END LOOP; END;

Output:

Explanation:

The loop iterates over each unique combination of id, name, and rank.
For each unique row, we count its occurrences in the table.
If any row count exceeds 1, it is printed as a duplicate row.

Method 2: Using the GROUP BY Clause

In this method, we will use the GROUP BY Clause to find the duplicate rows from our table. The GROUP BY Clause groups the rows based on some specified criteria.

Query:

DECLARE     --you can define any varibale (if required), in this approach we do not need any so its empty BEGIN   FOR i IN (     SELECT id, name, rank, COUNT(*) as c     FROM geeksforgeeks     GROUP BY id, name, rank     HAVING COUNT(*) > 1   ) LOOP     DBMS_OUTPUT.PUT_LINE('duplicate rows ID: ' || i.id ||  ' and name: ' || i.name || ' and rank: ' || i.rank || ' having count: ' || i.c);   END LOOP; END;

Output:

Explanation:

The GROUP BY clause groups rows by id, name, and rank.
The COUNT(*) function within the query counts the number of times each unique combination appears.
The HAVING clause filters rows where the count is greater than 1, identifying only duplicates

Conclusion

Overall, duplicate data detection is very common in data analysis tasks. We can identify duplicate data very easily in PL/SQL. We can use the GROUP BY Clause to create unique combinations of rows and perform operations over it very easily. We have seen a basic approach as well as an approach that includes the GROUP BY Clause. The GROUP BY Clause makes it very easy to detect duplicate data in a PL/SQL environment. Now you have a good understanding of detecting duplicate data in a PL/SQL environment. Now you can write queries related to it and get the desired result.

How to Find Duplicate Records in SQL?

vishuvaishnav3001

Improve

Article Tags :