MSSQL: removing semi-duplicate entries
-
Hi, I have a table (call it Part) listing our products and their prices. Prices change every once in a while and my boss made a new entry for each product every time its price changed. A simplified illustration:
ID Name PriceEffectiveDate Price ---- ---- -------------------- ----- 1 Shoe 1-1-2006 $1 2 Glove 1-1-2006 $2 ... 41 Shoe 1-1-2007 $2 (etc)(I should mention there is no priceEffectivedate column, he used a col that was intended for something else!) Obviously this is horrible design, so I created a second table (PartPrice) with columns partID,Price,and EffectiveDate. Now I need to remove the later entries for each product, but keep the original ones, and make the corresponding entries in the PartPrice table... I'm not an experienced SQL developer, anyone have any ideas? There are two things to make the problem simpler: Every part has a unique name, and the amount of parts have stayed constant, so each part has its entries a constant amount apart (i.e. Shoe will have ID's 1, 41, 81; glove will have 2,42,82 etc) Any hints/tips/ideas? -
Hi, I have a table (call it Part) listing our products and their prices. Prices change every once in a while and my boss made a new entry for each product every time its price changed. A simplified illustration:
ID Name PriceEffectiveDate Price ---- ---- -------------------- ----- 1 Shoe 1-1-2006 $1 2 Glove 1-1-2006 $2 ... 41 Shoe 1-1-2007 $2 (etc)(I should mention there is no priceEffectivedate column, he used a col that was intended for something else!) Obviously this is horrible design, so I created a second table (PartPrice) with columns partID,Price,and EffectiveDate. Now I need to remove the later entries for each product, but keep the original ones, and make the corresponding entries in the PartPrice table... I'm not an experienced SQL developer, anyone have any ideas? There are two things to make the problem simpler: Every part has a unique name, and the amount of parts have stayed constant, so each part has its entries a constant amount apart (i.e. Shoe will have ID's 1, 41, 81; glove will have 2,42,82 etc) Any hints/tips/ideas?Assuming SQL Server - I would suspect that the queries would look something like these:
INSERT INTO PartPrice ( partID, Price, EffectiveDate ) SELECT (SELECT MIN(ID) FROM Part WHERE Name = p.Name) AS ID, Price, WrongDateColumn FROM Part p DELETE Part WHERE PartId NOT IN (SELECT MIN(ID) FROM Part GROUP BY Name)