Oracle - How to remove the duplicate value row using self join in Oracle !! please assist

GeekInterview.com

I am new, Sign me up!

GeekInterview.com > Tech FAQs > Oracle

Go To First | Previous Question | Next Question

Oracle | Question 108 of 249

Print

How to remove the duplicate value row using self join in Oracle !! please assist

Total Answers and Comments: 6

Last Update: November 29, 2007 Asked by: jambesh

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

January 15, 2007 06:27:19

Member Since: January 2007 Contribution: 29

RE: How to remove the duplicate value row using self j...

Try Distinct clause ......

Is this answer useful? Yes | No

February 17, 2007 09:38:19

kasi

RE: How to remove the duplicate value row using self j...

suppose a table test contains
--------------------------------------
ename eno
-------------------------------------
kasi 1

kasi 1

This duplicate can be removed using rowid with self join

delete * from test where rowid>(select Min(rowid) from test t where t.ename test.ename);

Is this answer useful? Yes | No

February 18, 2007 01:05:35

Member Since: January 2007 Contribution: 29

RE: How to remove the duplicate value row using self j...

Ok kasi sir by using this query we can only delete the duplicate value or whole data will be deleted....

Is this answer useful? Yes | No

September 08, 2007 04:34:30

anonymous

RE: How to remove the duplicate value row using self j...

Here in this query the rowid (which is assigned to every row for every row of table ion oracle is used to identify and delete the duplicate rows in the table)as there is no other way of doing the same.

The smallest row id of the row is taken for comparing the other rowids and delete the one greater than the comparision value.

Is this answer useful? Yes | No

October 12, 2007 02:50:02

Mital Mehta

RE: How to remove the duplicate value row using self j...

Kasi you are right but the syntax is Wrong .
if u write delete * it will not work .

u have to write like this way

delete from test where rowid>(select Min(rowid) from test t where t.ename test.ename)

Is this answer useful? Yes | No

November 29, 2007 04:18:27

Member Since: August 2006 Contribution: 7

RE: How to remove the duplicate value row using self join in Oracle !! please assist

delete table_name where col_name in(select col_name from table_name group by
col_name having count(*) > 1)

Is this answer useful? Yes | No

Sponsored Links

About Us - Privacy Policy - Terms and Conditions - Contact - Ask Question - Propose Category - Site Updates

Copyright © 2005 - 2010 GeekInterview.com. All Rights Reserved