Thread: SQL Query Help

I have a nice split in my product database between products with just one variant and ones with multiple variants. I have created two xml packages to display these products. I don't really want to change them one at a time in the admin section.

I am trying to write a sql query to update all the products that have more than one variant to a specific xml package. Then do the same for the products that have just one.

I might just be having a mental block but I just can't seem to get it ironed out. Any help would be greatly appreciated.

Chaz.

You could use this to select the items:

select *
FROM Product
WHERE ((SELECT COUNT(VariantID) AS Number
FROM ProductVariant
WHERE (Product.ProductID = ProductID)) > 1)

Usual proviso: BACKUP YOUR PRODUCTION DATABASE FIRST, AND TRY THIS ON YOUR DEVELOPMENT DATABASE BEFORE APPLYING TO YOUR PRODUCTION.

Code:

-- this will do for your products with only 1 variant
update product
set xmlpackage = 'product.simpleproduct.xml.config'
where productid in (
select productid from productvariant
group by productid
having count(variantid)=1
)

-- this will do for your products with more than one variant
update product
set xmlpackage = 'product.variantsindropdown.xml.config'
where productid in (
select productid from productvariant
group by productid
having count(variantid)>1
)

Change XMLPackage name accordingly.

Awesomeness! Pure Awesomeness!

I really appreciate it. I don't know what was up I just couldn't get my head around it. Thanks so much for you help.

Chaz.