I have a table in which data in a column is "supposed" to be unique. The data for this table has been brought in from another database vendor which did not do a very good job enforcing uniqueness. Before I apply a unique constraint on the appropriate column, I need to check for uniqueness and clean up the data.

Sure enough, I found a number of instances where data is duplicated. For example, in the results below, the CustNum column value is supposed to be unique, but as you can see, there are duplicates.

ID CustNum Name Status

1 29226 Bob A
14 29226 Bobby A

4 2207 Larry A
27 2207 Larry A

9 4704 Jim I
56 4704 Jimmy A
99 4704 Jimbo A

What I would like to do is, for each duplicate pair, take one of the records and change the custnum value to another number (defined by me). If this were just a onetime deal, i would do it somewhat manually, however this will not be a onetime deal.

I am trying to find an "easy" way to do this. I have a query already that returns the duplicate information. Now i would like to loop through this data and change one of the numbers for each duplicate pair.

I have thought of one way to do it but it is not very elegant. Any information would be greatly appreciated. Hopefully I explained this ok.

Thanks...

Scott

__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Sounds like a perfect job for a cursor. Where you save the previous value into a variale, itterate to the next row, then if the previous value matches the current value, you change it as you described. Are you familiar with cursors? This is basically one of their most useful areas.

Yes, I'm very familiar with cursors, and that is the way I started to solve this problem, before stopping to see if there was a more efficient method. But, I think I agree with you. It looks like a cursor is the only way.