RDBMS - Hi all...Can you please tell me how to delete the duplicate records in a table.

GeekInterview.com

I am new, Sign me up!

GeekInterview.com > Tech FAQs > RDBMS

Go To First | Previous Question | Next Question

RDBMS | Question 11 of 18

Print

Hi all...
Can you please tell me how to delete the duplicate records in a table.

Total Answers and Comments: 8

Last Update: January 23, 2008 Asked by: vvk.kotamraju

Sponsored Links

Best Rated Answer

—

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

November 04, 2006 00:36:32

#1

Anonymous

RE: Hi all...Can you please tell me how to delet...

You can avoid duplicate records from table by using disinct keyword.

eg: select distinct uid from table1.

Is this answer useful? Yes | No

November 06, 2006 00:32:12

#2

Das

RE: Hi all...Can you please tell me how to delet...

delete from table a where a.rowid ! (select max(b.rowid) from table b where a.column1 b.column1)

Is this answer useful? Yes | No

December 07, 2006 10:06:10

#3

praveen

RE: Hi all...Can you please tell me how to delet...

the above given answer is correct but in place of the operator ! you use not in opearator it will deffinetly works.

Is this answer useful? Yes | No

February 02, 2007 05:46:28

#4

RE: Hi all...Can you please tell me how to delet...

create table tab2(a int b int);delete from tab2 a where (a.rowid) > ( sel min(b.rowid) from tab2 b where a.a b.a);or delete from tab2 a where (a.rowid) < ( sel max(b.rowid) from tab2 b where a.a b.a);

Is this answer useful? Yes | No

July 31, 2007 06:38:41

#5

Venkat

RE: Hi all...Can you please tell me how to delet...

SET ROWCOUNT 1;
DELETE FROM <TABLENAME>
WHERE <COLOUMN NAME> IN
(SELECT <COLOUMN NAME>
FROM <TABLENAME>
GROUP BY <COLOUMN NAME>
HAVING count(*) > 1)

Is this answer useful? Yes | No

October 25, 2007 08:46:21

#6

Kiran Kumar

RE: Hi all...Can you please tell me how to delet...

delete from tablename where rowid not in (select max(rowid) from tablename group by columnname)

Is this answer useful? Yes | No

November 19, 2007 06:24:47

#7

sowmya

RE: Hi all...Can you please tell me how to delet...

here ! works bcoz the inner query returns only a single value. NOT IN is used when multiples values are returned by the inner query

Is this answer useful? Yes | No

January 23, 2008 04:49:48

#8

Member Since: September 2007 Contribution: 4

RE: Hi all...Can you please tell me how to delete the duplicate records in a table.

find the rowid for the duplicate row and delete the max id no keeping the min id. thats it...

Is this answer useful? Yes | No

Sponsored Links

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

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