How to delete duplicate rows in a table

[saritha_racharla]

Hi,
this is saritha.

[ashalalaxmi2002]

hi

by using the rowid we can delete the duplicate rows.

ex:using emp table

delete from emp where rowid not in(select max(rowid) from emp group by empno);

[debasisdas]

You can also try this

Delete from emp e
where rowid>(select min(rowid) from emp
where e.empno=empno)

[pande.dhiraj]

hi

by using the rowid you can delete the duplicate rows from the table

beacause rowid is the unique for the every rows so that by using this you can do it .

delete from [table_name] where rowid not in(select max(rowid) from [table_name] group by [field_name]);

regards

[krishnaindia2007]

You can also do in the following ways
1)delete from emp x
where rowid <> (select max(rowid) from emp y
where x.empno = y.empno)
2)delete from emp x
where exists (select * from emp y
where x.empno = y.empno and x.rowid > y.rowid)

[susarlasireesha]

Try this
Delete from table_name a
where rowid>(select min(rowid) from
Same_table_name b where b.pk_column_1=a.pk_column_1
and b.pk_column_2=a.pk_columb_1);

[Mohini Jadhav]

There are two ways of Deleting the Duplicate rows Based on which Row you want to keep as it is .

1]If you want to delete rows ,keeping minimum rowid row then use Following query ,considering Emp table having empno as key

Delete from emp e
where rowid>(select min(rowid) from emp
where empno=e.empno)

1]If you want to delete rows keeping maximum rowid row , then use Following query

Delete from emp e
where rowid<(select max(rowid) from emp
where empno=e.empno)

[sureshkumar.mtech]

if I have 100 columns in the table?
delete duplicate rows but can not delete a record in the same name and different data.

[sureshkumar.mtech]

I tried following ways

delete from colum_3 a
2 where a.rowid >
3 (select min(b.rowid) from colum_3 b
4 where a.name = b.name
5 and a.age = b.age);

but i want exact result with deleting different data rows

[debasisdas]

your question is not clear ,can you pass more information.

[sureshkumar.mtech]

Actually i want delete duplicate rows in the table. If I have 100 columns and 1000 rows in the table how will delete the duplicate rows.
suppose employee table can have 100 columns and 1000 rows and same name present in the table multiple employe can have diffrent data. but i want keep the employe and i delete only duplicate rows of original one.

[sureshkumar.mtech]

Table can have a following data: name age location .... Department email ---------- ---------- --------- --------- -------- arun 25 chennai .... Hr sample@sample.com arun 25 chennai .... Hr sample@sample.com arun 25 chennai .... Hr sample@sample.com murugan 27 trichy .... Sales sample1@sample.com mani 28 chennai .... Software sample2@sample.com mani 35 trichy .... Marketing sample3@sample.com kathir 29 salem .... Software sample4@sample.com arun 33 chennai .... Manager arun@sample.com arun 33 chennai .... Manager arun@sample.com output should be name age location .... Department email ---------- ---------- --------- --------- -------- arun 25 chennai .... Hr sample@sample.com murugan 27 trichy .... Sales sample1@sample.com mani 28 chennai .... Software sample2@sample.com mani 35 trichy .... Marketing sample3@sample.com kathir 29 salem .... Software sample4@sample.com arun 33 chennai .... Manager arun@sample.com

[sureshkumar.mtech]

table can have a 100 column and 1000 rows. i want delete duplicate rows only. the table can have a employee data
the employee same name present in the table but different data and also present duplicate records in the table same name and same data. i want delete only duplicate rows. i can not delelete same name and same age and same department. we can only delte duplicate of original rows.
i tried above mentioned query. help me how will delete duplicate rows?.

[amitpatel66]

For deleting duplicate records, you can use the below query:

DELETE FROM emp where rowid NOT IN (SELECT MAX(rowid) FROM emp Group by empid);

The above query will delete the duplicate records which has same empid

Incase if you want to check for all the columns then try this query:

delete from emp where rowid not in (select max(rowid) from emp group by id,name,no)

My emp table had 3 columns id,name,no and you need to group by all the columns in order to check if any column contains different value or not.if yes then that record will not be considered as duplicate and will not be deleted. The above query will delete the record only when all the columns have same value

[sureshkumar.mtech]

this query only possible when we are using 3 or 4 columns but when i used 100 or more column how it is possible group by all the 100 columns.
i already tried this query any way thanks for your time .

[amitpatel66]

You will need to GROUP BY all the columns in case if you are looking at different value for any of the column. There is no other way around.