SQL Duplicates Issue SQL SERVER 2000
I have two tables, Product and ProductRateDetail. The parent table is a Product. I have duplicate entries in the product table that need to be unique. The ProductRateDetail table has records that 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 duplicates from the product table. I would do it manually, but there are 100 entries. that is, something like
UPDATE tbl_productRateDetail SET productID = (originalID from tbl_product)
then something like
DELETE from tbl_product WHERE duplicate ID
and remove only recently added id data
example: (sorry it can't work with this formatting)
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
a source to share
Not tested, so the syntax may have some errors.
First take the minimum product id from the grouped ones and update the entries in ProductRateDetail
UPDATE prd1
SET prd1.ProductID = p2.ProductID
FROM ProductRateDetail prd1
INNER JOIN Product p1 ON
p1.ProductID = prd1.ProductID
INNER JOIN
(
SELECT MIN(ProductID) AS ProductID, ProductTypeID, ProductDescription, ProductCode, ProductSize
FROM Product
GROUP BY ProductTypeID, ProductDescription, ProductCode, ProductSize
) p2 ON
p1.ProductTypeID = p2.ProductTypeID AND
p1.ProductDescription = p2.ProductDescription AND
p1.ProductCode = p2.ProductCode AND
p1.ProductSize = p2.ProductSize
Then remove the grouped products than the "selected" id
DELETE p1
FROM Product p1
INNER JOIN
(
SELECT MIN(ProductID) AS ProductID, ProductTypeID, ProductDescription, ProductCode, ProductSize
FROM Product
GROUP BY ProductTypeID, ProductDescription, ProductCode, ProductSize
) p2 ON
p1.ProductTypeID = p2.ProductTypeID AND
p1.ProductDescription = p2.ProductDescription AND
p1.ProductCode = p2.ProductCode AND
p1.ProductSize = p2.ProductSize AND
p1.ProductID != p2.ProductID
a source to share
something like this should work:
1) update the product. Details that duplicate links.
UPDATE tbl_productRateDetail d
SET productID = (SELECT MIN(p0.productID)
FROM tbl_product p0
JOIN tbl_product p1
ON p1.ProductTypeID = p0.ProductTypeID
AND p1.ProductDescription = p0.ProductDescription
AND p1.ProductCode = p0.ProductCode
AND p1.ProductSize = p0.ProductSize
WHERE p1.productID = d.productId)
WHERE productID IN (SELECT productID
FROM tbl_product
WHERE (ProductTypeID,
ProductDescription,
ProductCode,
ProductSize) IN
(SELECT ProductTypeID,
ProductDescription,
ProductCode,
ProductSize
FROM tbl_product
GROUP BY ProductTypeID,
ProductDescription,
ProductCode,
ProductSize
HAVING COUNT(*) > 1));
2) Detective duplicates
DELETE FROM tbl_product p0
WHERE productID <> (SELECT MIN(productID)
FROM tbl_product p1
WHERE p1.ProductTypeID = p0.ProductTypeID
AND p1.ProductDescription = p0.ProductDescription
AND p1.ProductCode = p0.ProductCode
AND p1.ProductSize = p0.ProductSize);
a source to share