Smrati Saxena

• Apr 3rd, 2006

 

Hi

You can delete the duplicate rows by using row ids ...just try this query..

Delete from Table_name a
 where
rowid !=(select min(rowid) from table_name b
         where a.any_common_col_name=b.any_common_column_name )
 

Dinesh

• Apr 10th, 2006

 

Hi I think it will not work

try this

delete from <table_name> a where rowid not in(select max(rowid) from <table_name> b where a.column_name=b.column_name;

Regards

Dinesh

dhk

 Profile Answers by dhk 

• Apr 20th, 2006

 

Hi Friend

          You can work it out by this sql statement

          select a.* from emp a where rowid>(select min(rowid) from emp b

                                                              where              a.commoncolumn=b.commoncolumn)

vishal.nelgi

 Profile Answers by vishal.nelgi 

• May 5th, 2006

 

Hitry this one ,it will workSQL> delete from TABLE_NAME where ROWID not in (select MAX(rowid) from TABLE_NAME GROUP BY COLUMN_NAME);COLUMN_NAME in the group by clause must be the column in which there are duplicate rows;for any furter clarification please replay..........Vishal Nelgi

Fazlur Rahiman

• Jul 4th, 2006

 

--Example of Simple Duplicate Records Deletioin

-- To Create A Table Structure
Create Table Emp
(
 EmpNo Int,
 EmpName Varchar(20),
 Address Varchar(20),
 City Varchar(20)
)

--Insert the Emp Table in Values

Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')
Insert Into Emp Values(2,'Kalyan','Broad Way','Chennai')
Insert Into Emp Values(3,'Vijay','Mount Road','Chennai')
Insert Into Emp Values(4,'Kumar','Parys','Chennai')
Go

-- To Insert a Duplicate Records
Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')
GO

--Create temp table to hold duplicate Table (#EmpTemp)
Create Table #EmpTemp
(
 EmpNo Int,
 EmpName Varchar(20),
 Address Varchar(20),
 City Varchar(20)
)

--Identify and save duplicate data into #EmpTemp
Insert Into #EmpTemp
Select * From Emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1

--Delete duplicate from original table
Delete From Emp Where
Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) in (
Select Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) From emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1)

--Insert the delete data back
Insert Into Emp
Select * From #EmpTemp

--Check for dup data.
Select * From Emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1

--Check table
Select * From Emp

--Drop temp table
Drop Table #EmpTemp

 

mahi

• Sep 14th, 2007

 

delete from table_name where rowid not in(select max(rowid) from table_name  groub by any_column_in_table);

KG

• Oct 2nd, 2007

 

Create table testDuplicate(
 tid number(3),
 tname varchar2(100));

       TID TNAME
-------------------------
         1 k
         1 k
         2 a
         2 a
         3 b
         3 b
  4 c

DELETE FROM testduplicate
a WHERE rowid NOT IN (select max(rowid) FROM testduplicate b
WHERE a.tname=b.tname AND a.tid=b.tid);

       TID TNAME
-------------------------
         1 k
         2 a
         3 b
  4 c

lanka_satya

 Profile Answers by lanka_satya 

• Jul 4th, 2008

 

dear friend,
It was a nice question,actually i have faced this type of problem many times....i will give the solution

try to understand the logic:

see if you table consists fo duplicate records means there is no constraints or constraints are not properly defined.


let us take the following context:
        empno ename sal
rec1:1231   xyx       3000
rec2:1231   ert        3456
rec3:1231   xyx       3000
rec4:1231   ert        3456
rec5:2345  ggh        5678


so if you can observe we have repetions in data,so now you try to do one thing try join the same table to itself.............and query using inline subquery.....

so here in the above context if you can observe on the basis of empno i can data is been duplicated that means if empno(1231) appears more than once i need to delete the records..

 delete from emp e where 1>(select count(*) from emp where empno=e.empno)

here if count is more than one iam deleting those records

any doubts
revert back to me..............

srividhya_85

 Profile Answers by srividhya_85 Questions by srividhya_85

• Jul 5th, 2008

 

Hi,
 
 'X' refers here to the table name and 'a' and 'b' are the alias name

Delete from X a
 where rowid in (select min(rowid) from X  b
                            where a.rowid = b.rowid);

gcvpgeek

 Profile Answers by gcvpgeek 

• Mar 16th, 2009

 

Use this simple query

delete from table1 where rowid not in (select max(rowid) from table1 group by duplicate_col_name)