HI all,

Would some please be able to help me out here, I have a table in which I need to select all duplicate records and then eventually delete them [Sybase dB]

Can someone pls help me out here with the syntax for the select and then the delete.

The Table has no Primary key @ the moment, as soon as I've cleansed that data i'll assign them.

Many thanks

select col1, col2, col3
from yourtable
group by col1, col2, col3
having count(*) > 1

here col1, col2, col3 are the columns which you want to screen for duplicate combinations

since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?

and if all but one, how will you determine which one? i.e. what other column will be used for this?


rudy

Thanks Rudy......

since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?
>>>>> I would want to delete all duplicates...only keep on instance of the record

and if all but one, how will you determine which one? i.e. what other column will be used for this?
>>>>>I don't have an Index column, so I'm I'm not too sure how this can be done....
I was thinking of Creating a new sybase table and assign my primary/indez keys and then try to import the data from the original tanle into the new one, all duplicated inserts that violate the primary key would then be aborted...?

But not to sure how to import that data once I've created the structure of the table...

What do you think???

Thanks,
Rocks

sorry for the delay replying

if you have a primary key in mind, perhaps you could give a few examples of so-called duplicated rows and which one of the group you might want to keep

rudy

If you only have one non-key value:

Select a.key1, a.key2, ..., a.val
FROM table a
WHERE a.val IN (SELECT TOP 1 val from table b
WHERE a.key1 = b.key1 and a.key2 = b.key2 and...)

Then you realize that it's nigh-impossible to compare rows in SQL and you wind up doing something with cursors.

Managed to sort this out!

Thanks for the help!!!

Rocks