Duplicates problem in datetime field, help!!

[friendlystar99]

I have a table with a 'datetime' column. I need to make this as the Primary Key. But, currently there are lot of duplicates in this datetime column, which is not allowing me to make it as PK. How do I update the data in this field which makes it unique in all the rows, at the same time the date part remains same as old.
Probably, I need to add few milliseconds of 'time' for each row in an increasing fashion, like 1 ms to 1st row, 2 ms to 2nd row, 3 ms to 3rd row etc..?
There are million rows in the table, only 1000 of them are duplicates. Any help?

[Sinapra]

Well, I believe its always better to keep some numeric column as PK and make the date field unique. You can always remove the duplicate rows.

Regards

[friendlystar99]

I need to keep this as part of a composite PK. I can't avoid it..so someone please help me clean up existing data. I can't delete the dups but I should update the date field by few milliseconds so as to make them unique.

[friendlystar99]

Anyone to help??

[Rawhide]

Updating the data to make it unique would be a bad way to go. Either it is not the right field to use as part of a composite primary key or you need to add an additional field to the primary key.

[friendlystar99]

Yeah, but I have to do it this way to fix the existing data. There is no other choice of columns. Any Gurus to help update the data?

[Rawhide]

Query for the duplicates. Add 1 millisecond to one of each duplicate and keep looping through until all duplicates are gone.

[DBFan]

looks like a good idea..can you give me the example code?

[friendlystar99]

Thanks Rawhide,

I will try that.