|
|
Posted by Erland Sommarskog on 08/20/07 21:52
(teddysnips@hotmail.com) writes:
> I have been tasked with cleaning up the data. The object is to delete
> all duplicate rows, but without leaving any orphaned references. At
> the end of the exercise the rows in the tables should be:
>
> CarID CarType
> 1 Ford Focus
> 3 Ford Galaxy
>
> PersonName CarID
> Adam Smith 1
> Ben Smith 1
> Colin Smith 3
> Dave Smith 3
> Edward Smith 3
> Fred Smith 3
>
> Note that the CarID for Fred Smith has been updated to the
> predominating row for the Ford Galaxy type.
UPDATE ExampleCustomer
SET CarID = Mincar.CarID
FROM ExampleCustomer C
JOIN ExampleCar Car ON C.CarID = Car.CarID
JOIN (SELECT CarType, CarID = MIN(CarID)
FROM ExampleCar
GROUP BY CarType) AS Mincar ON Mincar.CarType = Car.CarType
DELETE ExampleCar
FROM ExampleCar C
WHERE EXISTS (SELECT *
FROM ExampleCar C1
WHERE C1.CarType = C.CarType
AND C.CarID > C1.CarID)
There is no foreign declared in the example, but I hope that there is
in the real case.
Of course, risk is that your uzer have spelt the duplicates differently.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|