
One issue I have is that the pairs don't appear in the same order, the NULL value might be second.
I found an example of a use of MERGE but I'm not sure that this will be an applicable approach?
MERGE prospects USING ( SELECT max(id) id, companyname, max(address) address, max(city) city, max(zipcode) zipcode, max(total_cars) total_cars FROM prospects GROUP BY companyname ) NewData ON prospects.id = NewData.id WHEN MATCHED THEN UPDATE SET prospects.companyname = NewData.companyname, prospects.address = NewData.address, prospects.city = NewData.city, prospects.zipcode = NewData.zipcode, prospects.total_cars = NewData.total_cars WHEN NOT MATCHED BY SOURCE THEN DELETE; GO
I am able to write a statement that will compare rows with their preceding value (which I am using to, for example, fill down values) but will this help if the duplicated rows could be in either order?