Sybase - Given a table which contains some rows with duplicate keys, how would you remove the duplicates?

GeekInterview.com

I am new, Sign me up!

GeekInterview.com > Interview Questions > Database > Sybase

Go To First | Previous Question | Next Question

Sybase | Question 3 of 64

Print

Given a table which contains some rows with duplicate keys, how would you remove the duplicates?

Total Answers and Comments: 7

Last Update: March 03, 2009 Asked by: Pat

Sponsored Links

Best Rated Answer

—

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

August 15, 2006 15:59:09

#1

Kosmos

RE: Given a table which contains some rows with duplic...

select distinct * from table_1 into temp_table_1

truncate table_1

insert into table_1

select * from temp_table_1

This is one of the ways to eliminate duplicates .

Is this answer useful? Yes | No

September 27, 2006 09:55:50

#2

Ishwar Patil

RE: Given a table which contains some rows with duplic...

create temporary table with same table structure and then create unique index on this temporary table with option ignore_dup_row.

now insert data into temp table from the table in which duplicate records exists.

while inserting record into temp table duplicate rows will get ingored.

Finally temp table will have unique records

Is this answer useful? Yes | No

March 29, 2007 03:41:26

#3

siddhartha_sanyal

Member Since: March 2007 Contribution: 8

RE: Given a table which contains some rows with duplic...

That will not work.
'coz as soon as you try to make a unique clustered index with 'ignore_dup_row' option an error message will be encountered as " unique and ignore_dup_row are mutually exclusive ".

Is this answer useful? Yes | No

September 20, 2007 13:43:52

#4

dkreddi

RE: Given a table which contains some rows with duplic...

We can use clustered index with ignore_dup_row.

Is this answer useful? Yes | No

October 17, 2007 11:56:27

#5

Vikas

RE: Given a table which contains some rows with duplic...

Suppose there is a table Employee having 2 rows with same data.
Then in Sybase we can remove anyone by following command:
Step 1: Run 'SET ROWCOUNT 1'

Step 2: Run
'DELETE from EMPLOYEEwhere EMPLID '00091147' '

Step 3: Run 'SET ROWCOUNT 0'

Is this answer useful? Yes | No

October 25, 2007 14:10:59

#6

Amit Chauhan

RE: Given a table which contains some rows with duplic...

But this will remove all the db constraints from the table.... am I right...
so we can do the same like as follows:

select * into temp_1 from Original ---so no constraints are dropped

truncate table Original

insert into Original

select distinct * from temp_1

drop table temp_1

Is this answer useful? Yes | No

March 03, 2009 14:17:53

#7

Member Since: March 2009 Contribution: 9

RE: Given a table which contains some rows with duplicate keys, how would you remove the duplicates?

We can do remove the duplicate records using the below single query

Delete from table1 where rowid not in (select max(rowid) from table1 group by dup_col)

Is this answer useful? Yes | No

Sponsored Links

Related Categories

—

Sponsored Links

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

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