Raves, rants, views, reviews, comments and experiences of a Common IT Man.

Wednesday, March 31, 2010

Deleting duplicate rows in Sybase

I had to delete a set of duplicate rows in Sybase and that had me in a quandry. I had done that many-a-times in Oracle using the ROWID psuedo-column but nothing similar is available in Sybase or for that matter in SQL Server. Here's how to go about this daunting task in Sybase!

The deletion of duplicate rows cannot be done in a single SQL query like it can be done in Oracle. Hence it needs to be done in 3 steps:

Create temporary table with one instance of each distinct row

Delete all rows having multiple instances

Move back the unique data from temporary table to actual table

Step 1:
Create temporary table with one instance of each distinct row with the below query.

Note: that this step creates the temp_table_name table so it will fail if a table by that name already exists.

This step puts one instance of each distinct row into the temporary table. However, some of the rows may contain identical values in the key columns but different data in the remaining columns. At this point you need to check the data in the temp_table_name and reconcile which of the rows you wish to keep for a given duplicate key value. You may even need to do these three steps on the temporary table to achieve a set of unique records in the temporary table.

Step 2:
Delete all rows having multiple instances with the below query.

Step 3:
Move back the unique data from temporary table to actual table with the below query.

insert into data_table_name
select * from temp_table_name

Remember to change the table & column names in the above queries.
Replace data_table_name with your actual table name,temp_table_name with your choice of name for temporary table andkeycol1..n with the actual table column names!