Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duplicate Records Expand / Collapse
Author
Message
Posted Friday, August 8, 2008 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 9:57 AM
Points: 3, Visits: 17
Hi I have serveral product files i import each day from different vendors
each file contains manufacturer part numbers, prices, weight, descriptions etc etc for roughly 10,000 products

I Import daily the relevant info from each vendor files into their own tables

and then import all of the tables into one big table for uploading to our website.

the problem is that there are duplicate products coming from different sources

is there a way to weed out the duplicates based on price? either remove duplicates once they are in the combined table leaving the one with the lowest price, or filter so the duplicates cant happen in the first place.

Thanks
Post #549646
Posted Sunday, August 10, 2008 5:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
You have to write a query that would find duplicates, and then decide (disable or delete) how to handle the duplicates. There's no way for us to know from which table you'd want to delete things.

You can use standard join syntax to find out which rows in which tables might be duplicates.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #549940
Posted Monday, August 11, 2008 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 6:05 AM
Points: 3, Visits: 51
CREATE TABLE Dups (NameId int, FirstName varchar(10), LastName varchar(10))
GO

INSERT INTO Dups VALUES (106, 'Robert', 'Aide')
INSERT INTO Dups VALUES (107, 'David', 'Koidl')
INSERT INTO Dups VALUES (108, 'Hongbo', 'Li')
INSERT INTO Dups VALUES (123, 'Robert', 'Aide')
INSERT INTO Dups VALUES (124, 'David', 'Koidl')
INSERT INTO Dups VALUES (125, 'Hongbo', 'Li')
INSERT INTO Dups VALUES (138, 'Robert', 'Aide')
INSERT INTO Dups VALUES (139, 'David', 'Koidl')
INSERT INTO Dups VALUES (140, 'Hongbo', 'Li')
INSERT INTO Dups VALUES (153, 'Robert', 'Aide')
INSERT INTO Dups VALUES (154, 'David', 'Koidl')
INSERT INTO Dups VALUES (155, 'Hongbo', 'Li')
GO

DELETE FROM dups
WHERE
EXISTS
(
SELECT
NameId
FROM
dups DupsInner
WHERE
DupsInner.FirstName = Dups.FirstName
AND DupsInner.LastName = Dups.LastName
AND DupsInner.NameId < Dups.NameId
)
GO

Post #550468
Posted Monday, August 11, 2008 1:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
The easiest solution is going to be deleting from the combined table, before the final upload. You could dump them in the load from the vendor-specific tables, but that's going to be much more complex.

My usual solution for deleting duplicates is to use a CTE and the Row_Number() function.

;with Products (ID, Row) as
(select ID, row_number() over (partition by ProductID order by Price)
from dbo.CombinedTable)
delete from Products
where Row > 1

I'm guessing on your table structure, name, etc., but that's the basic code.

Does that help?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #550567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse