SQL Duplicates Issue SQL SERVER 2000
- by jeff
I have two tables : Product and ProductRateDetail. The parent table is Product. I have duplicate records in the product table which need to be unique. There are entries in the ProductRateDetail table which correspond to duplicate records in the product table.
Somehow I need to update the ProductRateDetail table to match the original (older) ID from the Product table and then remove the duplicates from the product table. I would do this manually but there are 100's of records. i.e. something like
UPDATE tbl_productRateDetail SET productID = (originalID from tbl_product)
then something like
DELETE from tbl_product WHERE duplicate ID
and only delete the recently added ID data
example: (sorry can't work out this formatting thing)
tbl_Product
select * from dbo.Product where ProductCode = '10003'
ProductID ProductTypeID ProductDescription ProductCode ProductSize
365 1 BEND DOUBLE FLANGED 10003 80mmX90deg
1354 1 BEND DOUBLE FLANGED 10003 80mmX90deg
tbl_ProductRateDetail
SELECT * FROM [MSTS2].[dbo].[ProductRateDetail] WHERE ProductID in (365,1354)
ProductRateDetailID ProductRateID ProductID UnitRate
365 1 365 16.87
1032 5 365 16.87
2187 10 365 16.87
2689 11 365 16.87
3191 12 365 16.87
7354 21 1354 21.30
7917 22 1354 21.30
8480 23 1354 21.30
9328 25 1354 21.30
9890 26 1354 21.30
10452 27 1354 21.30
Please help!
