Thread: SQL help, again.

I recently imported some products using the XML product import and it went well, sort of. However, I didn't notice a small problem that occurred.

All of my products [should] have only one variant. Since the import some of my products have multiple variants. Being that I didn't catch this in time I don't have a backup I can roll back to.

Here is an example of what my data looks like:

Product Table
productid name
1 Product1
2 Product2
3 Product3

Variant Table
variantid productid name Price
11 1 Variant-11-1 0.01
22 1 Variant-22-1 0.75
33 1 Variant-33-1 2.00
44 2 Variant-44-2 2.00
55 3 Variant-55-3 503.00
66 3 Variant-66-3 1,000.00
77 3 Variant-77-3 75.00

What I am not able to do is get my variant table to look like this

Variant Table
variantid productid name price
33 1 Variant-33-1 2.00
44 2 Variant-44-2 2.00
66 3 Variant-66-3 1,000.00

Essentially I need to keep only one variant for each product and it needs to be the most expensive variant.

Now I have done some reading up on Cursors because I believe this is what is needed to accomplish this but I haven't been able to get the results I am after.

I may be going about this all wrong by using a cursor, so I am looking for a little help.

Any direction, guidance, or suggestions would be greatly appreciated.

Thanks in advance

Chaz.

You will definitely want to make a backup before you run this. Maybe even perform this first on your test server to make sure it does what you want. I have tried this on 7 products and 12 variants. The only modifications you would have to make are to change the product ID's. In my example, I'm using my development database product id's of 1,2,3,4,5,6,7.

Code:

DECLARE @DeletePV TABLE
(
	rownum int,
	variantID int, 
	productID int
)
INSERT INTO @DeletePV (rownum,VariantID,productID)
(SELECT ROW_NUMBER() OVER (PARTITION BY productID ORDER BY price DESC), 
VariantID, ProductID
FROM ProductVariant WHERE ProductID IN (1,2,3,4,5,6,7) AND Published = 1 AND Deleted = 0
)

--DELETE FROM ProductVariant WHERE variantID IN (SELECT VariantID FROM @DeletePV WHERE rownum > 1)

** I commented out the DELETE line to make sure you know what you are doing before you run the delete command. Feel free to ask if you have any questions.

Chris, thanks very much!

Your SQL worked flawlessly. Thank you so much. I am so happy I decided to post the question here because I was really going at the problem all wrong.

I actually just changed the last line to

C#/VB.NET Code:

update productvariant set deleted = 1 WHERE variantID IN (SELECT VariantID FROM @DeletePV WHERE rownum > 1) 


Doing the soft delete gave me the ability to "undo" anything if I needed being that I did not have any soft deleted variants to begin with.

Needless to say there was not a single problem and everything work perfect.

Thanks again.

Chaz.