Deleting duplicates rows from oracle

Question

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

@ZanShah120 - Do you care which row you retain? Do you care whether `employee_id` 1 stays in `department_id` 10 or 20? — Justin Cave, Mar 03 '12 at 08:22
@ZanShah120 - What does that mean? I believe you want to retain one row that has `employee_id` 1. There are no duplicate rows in the data you posted. There are two rows with `employee_id` 1 but they have different `department_id` values. Which of the two `department_id` values do you want to retain? Do you always want the largest `department_id`? The smallest? Something else? — Justin Cave, Mar 03 '12 at 08:26
@JustinCave there is an employee 1 which belongs to 2 departments hence he has duplicate enteries in the table,i want to delete anyone of the entry — require_once, Mar 03 '12 at 08:29
@ZanShah120 - So, you don't care whether `employee_id` 1 ends up in `department_id` 10 or 20? And you don't care whether `employee_id` 2 ends up in `department_id` 10 or 30? — Justin Cave, Mar 03 '12 at 09:18
possible duplicate of [Removing duplicate rows from table in Oracle](http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — Ben, Mar 20 '12 at 16:06

score 3 · Accepted Answer · answered Mar 03 '12 at 08:31

3

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

answered Mar 03 '12 at 08:31

Dawood ibn Kareem

77,785
15
98
110

score 2 · Answer 2 · edited Mar 19 '12 at 22:56

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.

*"many shops do not use RI because they need the flexibility"* And those shops inevitably pay the price in dealing with corrupted data. — APC, Oct 11 '19 at 10:04

score 0 · Answer 3 · edited Jan 06 '22 at 16:16

0

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);

edited Jan 06 '22 at 16:16

Shunya

2,344
4
16
28

answered Jan 04 '22 at 07:23

SoftwareEngineer

197
1
3

rahul404 · Answer 4 · 2017-06-01T10:32:21.843

-1

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

edited Jun 01 '17 at 10:32

answered Jun 01 '17 at 05:14

rahul404

29
1
5

Deleting duplicates rows from oracle

4 Answers4

Linked

Related