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

Home | Tech FAQ | Interview Questions | Placement Papers | Tech Articles | Learn | Freelance Projects | Online Testing | Geeks Talk | Job Postings | Knowledge Base | Site Search | Add/Ask Question

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

RDBMS FAQs Related

—

Sponsored Links

Sponsored Links

About Us | Privacy Policy | Terms and Conditions | Contact | Site Map | Add Question | Propose Category | RSS Feeds | Articles Sitemap | Site Updates | Add Resource

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