More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How do I remove duplicate rows from a table in Sybase?
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
You probably want to ensure you have a very good backup of the base_table at this point, coz your going to clear it out! You will also want to check to ensure that the temp_table includes the rows you need. You also need to ensure that there are no triggers on the base table (remember to keep a copy!) or RI constraints. You probably do not want any of these to fire, or if they do, you are aware of the implications.
Now you have a couple of choices. You can simply drop the original table and rename the temp table to the same name as the base table. Alternatively, truncate the table and insert from the temp_table into the original table. You would need to do this last if you did need the RI to fire on the table etc. I suspect that in most cases dropping and renaming will be the best option.
If you want to simply see the duplicates in a table, the following query will help:
select key1, key2, ...
from base_table
group by key1, key2, key3, key4, ...
having count(*) > 1
Sybase will actually allow a "select *", but it is not guaranteed to work.
(Continued on next question...)
