Query to eliminate dulicate rows

[ranjitkumar]

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example
[code=oracle]
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
[/code]

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

[minapatel]

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

Try where the row count is more than one delete the extra rows....

[VitorLeite]

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

Have you tried using
[code=oracle]
SELECT DISTINCT(empno) FROM Employee
[/code]
Oh, about the deletion part, i'm not really sure, but you should save the results of the 1st query, truncate the table, and insert them back in it again.

Hope it helps

[rkamuni]

Hi, You can use the follwoing query.
[code=oracle]
delete from employee e where e.rowid not in ( select min(b.rowid) from employee b where e.primarykeyfld = b.primarykeyfld )
[/code]

Hope this will solve ur issue.

Thanks,

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

[ck1004]

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

[code=oracle]
delete from employee where rowid not in (select max(rowid) from employee group by empno);
[/code]
or
[code=oracle]
delete from Employee emp where rowid <(select min(rowid) from Employee emp1 where emp.empno=emp12 .empno);
[/code]

[ck1004]

[QUOTE=ranjitkum ar]Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????[/delete from employee where rowid not in (select max(rowid) from employee group by empno);
or
delete from employee emp where rowid <(select min(rowid) from employee emp1 where emp.empno=emp1. empno); ]

[pragatiswain]

I am not really sure what you are really looking for.
Some times I do the following. It is much faster with huge data volume (Millions of records) and safe.
1. Insert distinct records into a backup table.
2. Truncate Original table.
3. Insert records in original table from backuptable.

Hope this helps

[suvam]

u may try with the either one to delete duplicate rows --->

[code=oracle]
1. Delete from T1 a
where a.rowid !=(Select min(b.rowid) from T1 b where b.pk_key = a.pk_key) ;
[/code]
[code=oracle]
2. delete from T1
group by pk_key
having count(pk_key) > 1 ;
[/code]

[akhileshkumar18]

Hi,

Do anyone know a query for the following senario,

I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???

For example

create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};

the content of the table is

emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50

how to delete the duplicate entries in the above table ????

Hi Ranjit,
You can try this
[code=oracle]
delete from Employee
where empno ='emp01'
AND name ='ranjit'
AND salary = 10000.50
AND rownum = 1
[/code]

Run this query one less then the no of records.
In this example there are 6 entries of the same so you have to run this query 5 times

Regards
Akhilesh

[amitpatel66]

All,

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[amitpatel66]

Try below query:

[code=oracle]
DELETE FROM table1 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM table1 GROUP BY empno HAVING COUNT(empno) > 1)
[/code]

The above query will delete all the older duplicate rows from the table keeping the latest inserted record in the table