-
Junior Member
How to delete duplicate rows in a table
-
Contributing Member
Re: How to delete duplicate rows in a table
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);
-
Re: How to delete duplicate rows in a table
You can also try this
Delete from emp e
where rowid>(select min(rowid) from emp
where e.empno=empno)
-
Junior Member
Re: How to delete duplicate rows in a table
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
-
Expert Member
Re: How to delete duplicate rows in a table
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)
-
Expert Member
Re: How to delete duplicate rows in a table
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);
-
Junior Member
Re: How to delete duplicate rows in a table
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)
-
Junior Member
Re: How to delete duplicate rows in a table
if I have 100 columns in the table?
delete duplicate rows but can not delete a record in the same name and different data.
-
Junior Member
Re: How to delete duplicate rows in a table
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
-
Re: How to delete duplicate rows in a table
your question is not clear ,can you pass more information.
-
Junior Member
Re: How to delete duplicate rows in a table
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.
-
Junior Member
Re: How to delete duplicate rows in a table
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
-
Junior Member
Re: How to delete duplicate rows in a table
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?.
-
Expert Member
Re: How to delete duplicate rows in a table
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
-
Junior Member
Re: How to delete duplicate rows in a table
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 .
-
Expert Member
Re: How to delete duplicate rows in a table
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules