Sybase Question:
How do I remove duplicate rows from a table in Sybase?
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
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
| Previous Question | Next Question |
| How to implement if-then-else in a select clause in Sybase? | The timestamp datatype in Sybase? |