Kosmos

• Aug 15th, 2006

 

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 .

Ishwar Patil

• Sep 27th, 2006

 

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

siddhartha_sanyal

 Profile Answers by siddhartha_sanyal 

• Mar 29th, 2007

 

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 ".

dkreddi

• Sep 20th, 2007

 

We can use, clustered index with ignore_dup_row.

Vikas

• Oct 17th, 2007

 

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'

Amit Chauhan

• Oct 25th, 2007

 

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

gcvpgeek

 Profile Answers by gcvpgeek 

• Mar 3rd, 2009

 

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)

krishna kanth

 Profile Answers by krishna kanth 

• Apr 14th, 2011

 

set rowcount 1

delete from mytable 

where (select count(*) from Mytable group by col1 having count(*)>1)>1

while(@@rowcount<>0)

delete from mytable 

where (select count(*) from Mytable group by col1 having count(*)>1)>1

set rowcount 0

go