CODEWITHSUNDEEP
sqloracle-databaseoracle10g
require_once
require_once

Reputation: 2055

Deleting duplicates rows from oracle

I am using oracle database.I want to use duplicate rows from a table except one,which means that I want to delete all rows but atleast one row should be there. I have a table

employee_id ---- department_id
1                     10
2                     10
1                     20
3                     30
2                     30

Now i want to delete duplicate rows but at least one row should be there.

select count(employee_id),employee_id 
from employee
group by employee_id
having count(employee_id) >1));

i had used this to find number of employees that are in more than one department but could not find a way to move further. If i use a delete there it will delete all duplicates,But i want to keep one copy.

delete from empl where eno IN( select eno from( select count(eno),eno from empl group by eno having count(eno) >1));

EDIT:I want to retain employee_id Any one who can guide me further

Upvotes: 1

Views: 1253

Answers (4)

SoftwareEngineer
SoftwareEngineer

Reputation: 437

You can use the pseudo column as the following statement:

DELETE FROM employee 
WHERE rowid not in
(SELECT MIN(rowid)
FROM employee 
GROUP BY employee_id);

Upvotes: 0

rahul404
rahul404

Reputation: 29

DELETE FROM names a WHERE ROWID > (SELECT MIN(ROWID) FROM names b WHERE b.name=a.name AND b.age=a.age

Upvotes: -1

Kara89r
Kara89r

Reputation: 21

Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );

Use RANK to delete duplicate rows

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, duplicate file finder many shops do not use RI because they need the flexibility.

Upvotes: 2

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79875

delete from employee a
where employee_id in (
   select employee_id 
   from employee b
   where b.department_id > a.department_id )

Upvotes: 3

Related Questions