Question Locked

Removing duplicate rows in Sybase off of two key fields

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm in the need to remove duplicate entries from a specific table. The primary key0 is a number which can be duplicated and then there's the key1 field which is a varchar which should not be duplicated. How these are there is another story but how do I clean these up?

2 replies

by tony hopkinson

Have you got a way of identifying the record you want to keep, say a date and most recent. Or can you just pick one and bin the rest.

Create a table, temporary if you want, to take the two key columns and your extra good data column(s).

Given you just wanted the earliest Varchar Key, and the number part was an auto incrementing key, then

Insert Into MyKeys(KeyNumber,KeyString)
Select Min(KeyNumber), KeyString) From KeyTable Group By KeyString
Gives you the ids of those you wish to keep.

With Those you can do
Insert Into CleanedUpKeyTable
Select KeyTable.* From KeyTable,MyKeys
Where KeyTable.KeyNumber = MyKeys.KeyNumber
and KeyTable.KeyString = MyKeys.KeyString

CleanedKeyTable has the same schema as the original except KeyString is the primary Key and KeyNumber is at most an index.
(I'm assuming that's what your blurb meant)

If your happy that it worked. Drop the original, rename CleanedUpKeys, and drop MyKeys if it wasn't temporary as well.

HtHs

This discussion is closed to new replies.