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)

select * from dbo.Product where ProductCode = '10003'

tbl_product

ProductID    ProductTypeID    ProductDescription       ProductCode     ProductSize
365          1              BEND DOUBLE FLANGED     10003           80mmX90deg
1354            1              BEND DOUBLE FLANGED     10003           80mmX90deg

SELECT * FROM [MSTS2].[dbo].[ProductRateDetail] WHERE ProductID in (365,1354)

tbl_productratedetail

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!