how to delete duplicate rows in the table?

by sridevi S
(bangalore)

hi... i have lot of confusion in queries..

please tell me
1. how to delete the duplicate rows in the table?
2. display the dept no, dept name and manager? (deptno, deptname from dept table)
(manager from employee table)

Comments for how to delete duplicate rows in the table?

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 25, 2015
Rating
starstarstarstarstar
Identifying duplicate rows in a table in Oracle
by: Anonymous

Needing to remove duplicate rows from a table in Oracle is quite a common problem and initially seems intractable. However the solution is actually quite straightforward. In fact there are 2 possible solutons.

Solution #1: you could use a client-side tool such as Oracle SQL Developer to display the table data and then manually go through and delete the duplicate rows. This is only practical though for a samll table with a small number of duplicates.

Solution #2: use SQL. As well as all columns you define in the table Oracle automatically includes (a normally) hidden column called rowid which is guaranteed to be unique. Therefore when you select the data from your table if yoy include teh rowid you now have a unique identifier which you can use to delete the rows you don't want.

For example, let's suppose you have an employee table which has the columns employee_id, employee_name, department_id and you should have a primary key constraint on employee_id but somehow this was dropped and you now have duplicate employee records which you need to remove before the next payroll run.

Running the query:

SELECT emp_id,emp_name FROM emp;

clearly shows duplicate records.

"EMP_ID","EMP_NAME"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"
2,"Jack Jones"
1,"Joe Smith"

However if we change the query to

SELECT rowid, emp_id FROM emp;

"ROWID","EMP_ID"
AAAXCJAABAAAYaBAAA,2
AAAXCJAABAAAYaBAAB,1
AAAXCJAABAAAYaBAAC,2
AAAXCJAABAAAYaBAAD,1
AAAXCJAABAAAYaBAAE,2
AAAXCJAABAAAYaBAAF,1
AAAXCJAABAAAYaBAAG,2
AAAXCJAABAAAYaBAAH,1
AAAXCJAABAAAYaBAAI,2
AAAXCJAABAAAYaBAAJ,1
AAAXCJAABAAAYaBAAK,2
AAAXCJAABAAAYaBAAL,1
AAAXCJAABAAAYaBAAM,2
AAAXCJAABAAAYaBAAN,1
AAAXCJAABAAAYaBAAO,2
AAAXCJAABAAAYaBAAP,1
AAAXCJAABAAAYaBAAQ,2
AAAXCJAABAAAYaBAAR,1
AAAXCJAABAAAYaBAAS,2
AAAXCJAABAAAYaBAAT,1

we can clearly see that each row is in fact unique even though we still have duplicate employee ids.

To remove the duplicates then, we need to delete all but one row for each of the sets of duplicate employeed ids.

This is where the Oracle rowid pseudo column comes in. By grouping the rowids per employee id and selecting the lwoest (min) rowid we are left with just one row per employee_id in the result set.

SELECT min(rowid),emp_id FROM emp GROUP BY emp_id

"MIN(ROWID)","EMP_ID"
AAAXCJAABAAAYaBAAB,1
AAAXCJAABAAAYaBAAA,2

Now all we need to do is to turn that into a subquery and prefix it with a delete claue and we're done.

DELETE FROM emp WHERE rowid NOT IN (
SELECT min(rowid) FROM emp GROUP BY emp_id
)

Note that we don't beed to select a column to group by it.

We can re-run the original query to prove that we only have one row per employeed id but it's not necessary except for peace of mind.

SELECT emp_id,emp_name FROM emp;

"ROWID","EMP_ID"
AAAXCJAABAAAYaBAAA,2
AAAXCJAABAAAYaBAAB,1

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.