Mapping problem - identify duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mapping problem - identify duplicates

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-24 : 11:04:50

Hi

I need to update a table to reflect correct mappings of Product References. However, if the value to which the product ref will be updated to already exists, I want to remove it:

I have a table #Sale with the following data:
ProductRef StoreID
2 1
3 1
10 1
20 1

I want to update the values '10' and '20' (old refs) to the new refs (10=1, 20=2).

However, if the ref already exists (in this instance, there is already a record for productRef 2 in #Sale), I want to remove it to a reporting table. See below

CREATE TABLE #ProductMapping
(
  ProductRef INT
, OldProductRef INT
, StoreID INT
)

INSERT INTO #ProductMapping
SELECT 1,10,1
UNION
SELECT 2,20,1
UNION 
SELECT 3,30,1
UNION
SELECT 1,10,2
UNION
SELECT 2,20,2
UNION 
SELECT 3,30,2


CREATE TABLE #Sale
(
  ProductRef INT
, StoreID INT
CONSTRAINT PK_Sale PRIMARY KEY (ProductRef,StoreID)
)

INSERT INTO #Sale
SELECT 10,1
UNION
SELECT 20,1
UNION 
SELECT 3,1
UNION 
SELECT 2,1

CREATE TABLE #Report
(
  ProductRef INT
, StoreID INT
, Date DATETIME
)

SELECT * FROM #Sale

-- This throws a valid error as there cannot be more than one entry for a ProductRef
UPDATE #Sale
SET ProductRef = m.ProductRef
FROM #Sale s INNER JOIN #ProductMapping m ON s.ProductRef = m.OldProductRef
AND m.StoreID = s.StoreID

-- INSERT INTO #Report the ProductRefs that cannot be mapped (already exist)

-- DELETE FROM #Sales the ProductRefs that cannot be mapped

-- RERUN the UPDATE script above

GO
DROP TABLE #Sale 
DROP TABLE #ProductMapping
DROP TABLE #Report

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-24 : 11:34:03

Don't worry, I worked it out:


CREATE TABLE #ProductMapping
(
  ProductRef INT
, OldProductRef INT
, StoreID INT
)
GO
INSERT INTO #ProductMapping
SELECT 1,10,1
UNION
SELECT 2,20,1
UNION 
SELECT 3,30,1
UNION
SELECT 1,10,2
UNION
SELECT 2,20,2
UNION 
SELECT 3,30,2
GO

CREATE TABLE #Sale
(
  ProductRef INT
, StoreID INT
CONSTRAINT PK_Sale PRIMARY KEY (ProductRef,StoreID)
)
GO
INSERT INTO #Sale
SELECT 10,1
UNION
SELECT 20,1
UNION 
SELECT 30,1
UNION 
SELECT 2,1
UNION 
SELECT 1,1
GO
CREATE TABLE #Report
(
  OldProductRef INT
, ProductRef INT
, StoreID INT
, Date DATETIME
)
GO
--SELECT * FROM #Sale

-- ERROR
--UPDATE #Sale
--SET ProductRef = m.ProductRef
--FROM #Sale s INNER JOIN #ProductMapping m ON s.ProductRef = m.OldProductRef
--AND m.StoreID = s.StoreID


-- INSERT INTO #Report the ProductRefs that cannot be mapped (already exist)
INSERT INTO #Report (  OldProductRef, ProductRef, StoreID, Date )
SELECT s.ProductRef AS OldProductRef, p.ProductRef AS ProductRef, s.StoreID, CURRENT_TIMESTAMP
FROM #Sale s
INNER JOIN #ProductMapping p ON p.OldProductRef = s.ProductRef AND p.StoreID = s.StoreID
INNER JOIN (SELECT DISTINCT StoreID, ProductRef FROM #Sale) s2 
ON s2.StoreID = s.StoreID AND s2.ProductRef = p.ProductRef

SELECT * FROM #Report

-- DELETE FROM #Sales the ProductRefs that cannot be mapped
DELETE FROM s
FROM #Sale s
INNER JOIN #ProductMapping p ON p.OldProductRef = s.ProductRef AND p.StoreID = s.StoreID
INNER JOIN (SELECT DISTINCT StoreID, ProductRef FROM #Sale) s2 
ON s2.StoreID = s.StoreID AND s2.ProductRef = p.ProductRef

-- RERUN the UPDATE script above
UPDATE #Sale
SET ProductRef = m.ProductRef
FROM #Sale s INNER JOIN #ProductMapping m ON s.ProductRef = m.OldProductRef
AND m.StoreID = s.StoreID

GO
DROP TABLE #Sale 
DROP TABLE #ProductMapping
DROP TABLE #Report

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:41:09

May be this

INSERT INTO #Report
SELECT s1.ProductRef,s1.StoreID
FROM #Sale s
INNER JOIN #ProductMapping pm
ON pm.OldProductRef=s.ProductRef
AND pm.StoreID=s.StoreID
INNER JOIN #Sale s1
ON s1.ProductRef=pm.ProductRef
AND s1.StoreID=pm.StoreID

DELETE s
FROM #Sale s
LEFT JOIN #ProductMapping pm
ON pm.OldProductRef=s.ProductRef
AND pm.StoreID=s.StoreID
WHERE pm.OldProductRef IS NULL

UPDATE s
SET s.ProductRef=pm.ProductRef
FROM #Sale s
INNER JOIN #ProductMapping pm
ON pm.OldProductRef=s.ProductRef
AND pm.StoreID=s.StoreID
LEFT JOIN #Sale s1
ON s1.ProductRef=pm.ProductRef
AND s1.StoreID=pm.StoreID
WHERE s1.StoreID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources