Thanks Sergey, I'll try that out.
>Create a table/temp table/table variable with the old and new Provider PK
>
>
>
>
>UPDATE ClaimItems SET cit_prvFK = ProviderChange.NewPK
> FROM ClaimItems
> JOIN ProviderChange ON ProviderChange.OldPK = ClaimItems.cit_prvFK
>
>
>
>
>
>>I've got one for the SQL people.
>>
>>I've got a database in SQL Server with a table called Providers with prv_PK the primary key. There is another table called ClaimItems that stores the Provider Key in its cit_prvFK field.
>>
>>The data was imported from a spreadsheet, so you know there are lots of spelling mistakes so the same provider might have ended up in the Providers table twice (or more) if there was a slight misspelling.
>>
>>So for example I have these as separate Providers:
>>
>>Superpharm
>>Superpharm Limited
>>Superpharm Limted
>>
>>and these:
>>
>>Tropical Pharmacy
>>Tropical Pharmcay
>>
>>Now, I need to clean up the data which means switching the cit_prvfk value to the prv_pk value of the Provider I want to keep and then deleting the other providers.
>>
>>This list of duplicates has about 100 providers. I can do this one by one, but it'll take ages. How would you suggest approaching this?