To remove duplicate supplier on ALL STORES
We need to issue 2 worksheet sql to all affected stores.
1. Create the stored procedure under all store.
* Issue Worksheet sql, select all stores.
* Under contents, add two rows.
* Copy and paste this query in 1st row (Priority 1)
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('fixMultipleSupplier') ) BEGIN DROP PROCEDURE fixMultipleSupplier END
* Next, copy and paste this query on 2nd row (priority 2)
CREATE PROCEDURE [dbo].[fixMultipleSupplier] @run AS BIT
AS
BEGIN
IF ( @run = 1 )
BEGIN
IF EXISTS ( SELECT hqid ,
supplierName ,
COUNT(*) AS myCount
FROM Supplier
GROUP BY hqid ,
supplierName
HAVING COUNT(*) > 1 )
BEGIN
IF OBJECT_ID('tempdb..#RetainSupplier') IS NOT NULL
DROP TABLE #RetainSupplier
SELECT tr.hqid ,
MIN(S.id) AS minID
INTO #RetainSupplier
FROM ( SELECT hqid ,
supplierName ,
COUNT(*) AS myCount
FROM Supplier
GROUP BY hqid ,
supplierName
HAVING COUNT(*) > 1
) AS tr
INNER JOIN dbo.Supplier s ON s.HQID = tr.hqid
GROUP BY tr.hqid
UPDATE Item
SET SupplierID = r.minID
FROM dbo.Item i
INNER JOIN dbo.Supplier s ON s.ID = i.SupplierID
INNER JOIN #RetainSupplier r ON r.hqid = s.HQID
WHERE i.SupplierID != r.minID
UPDATE dbo.SupplierList
SET SupplierID = r.minID
FROM SupplierList sl
INNER JOIN dbo.Supplier s ON s.ID = sl.SupplierID
INNER JOIN #RetainSupplier r ON r.hqid = s.HQID
WHERE sl.SupplierID != r.minID
UPDATE dbo.PurchaseOrder
SET SupplierID = R.minID
FROM PurchaseOrder po
INNER JOIN dbo.Supplier s ON s.ID = po.SupplierID
INNER JOIN #RetainSupplier r ON r.hqid = s.HQID
WHERE po.SupplierID != r.minID
UPDATE dbo.ItemClass
SET SupplierID = r.minID
FROM dbo.ItemClass ic
INNER JOIN dbo.Supplier s ON s.ID = ic.SupplierID
INNER JOIN #RetainSupplier r ON r.hqid = s.HQID
WHERE ic.SupplierID != r.minID
DELETE FROM dbo.Supplier
FROM dbo.Supplier s
INNER JOIN #RetainSupplier r ON r.HQID = s.HQID
WHERE s.ID != R.minID
END
END
ELSE
BEGIN
SELECT HQID ,
supplierName ,
Code ,
COUNT(*) AS RecordCount
FROM Supplier
GROUP BY hqid ,
supplierName ,
Code
HAVING COUNT(*) > 1
END
END
* Approve the worksheet.
2. Execute the stored procedure to fix duplicates.
* Issue worksheet sql to all stores.
* Under contents, copy and paste the query to 1st row (priority 1)
EXEC [fixMultipleSupplier] @Run=1
SYNC ALL AFFECTED STORES!
