Sybase Question:

Download Job Interview Questions and Answers PDF

How do I remove duplicate rows from a table in Sybase?

Sybase Interview Question
Sybase Interview Question

Answer:

There are a number of different ways to achieve this, depending on what you are trying to achieve. Usually, you are trying to remove duplication of a certain key due to changes in business rules or recognition of a business rule that was not applied when the database was originally built.

Probably the quickest method is to build a copy of the original table:

select *
into temp_table
from base_table
where 1=0


Create a unique index on the columns that covers the duplicating rows with the ignore_dup_key attribute. This may be more columns that the key for the table.

create unique index temp_idx
on temp_table(col1, col2, ..., colN)
with ignore_dup_key

Now, insert base_table into temp_table.

insert temp_table
select * from base_table

Download Sybase Interview Questions And Answers PDF

Previous QuestionNext Question
How to implement if-then-else in a select clause in Sybase?The timestamp datatype in Sybase?