Delete duplicate rows

Different ways of deleting duplicate rows in Oracle SQL

different_ways_of_deleting_duplicate_rows_in_oracle_sql

Consider the emp table with below rows


CREATE TABLE  
emp(
    empno INTEGER,
    empname VARCHAR2(20),
    salary NUMBER
    );
10 Bill 2000 11 Bill 2000 12 Mark 3000 12 Mark 3000 12 Mark 3000 13 Tom 4000 14 Tom 5000 14 Susan 5000

  1. Using ROWID

    DELETE FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY empno );

    This technique can be applied to almost scenarios. GROUP BY operation should be on the columns which identify the duplicates.

  2. Using self-join

    DELETE FROM emp e1 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp e2 WHERE e1.empno=e2.empno );

  3. Using ROW_NUMBER()

    DELETE FROM emp WHERE ROWID IN (SELECT rid FROM ( SELECT ROWID rid, ROW_NUMBER() OVER(PARTITION BY empno ORDER BY empno) rn FROM emp ) WHERE rn > 1 );

    This is another efficient way to delete duplicates.

  4. Using DENSE_RANK()

    DELETE FROM emp WHERE ROWID IN (SELECT rid FROM ( SELECT ROWID rid, DENSE_RANK() OVER(PARTITION BY empno ORDER BY ROWID ) rn FROM emp ) WHERE rn > 1 );

    Here you can use both RANK() and DENSE_RANK() , since both will give unique records when ORDER BY ROWID.

  5. Using GROUP BY

    DELETE FROM emp WHERE (empno,empname,salary) IN ( SELECT MAX(empno),empname,salary FROM emp GROUP BY empname,salary);


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.