Removing duplicate rows in Sybase off of two key fields

By garret.maracci ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Well that depends

by Tony Hopkinson In reply to Removing duplicate rows i ...

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.


Related Discussions

Related Forums