Thread: Help with SQL query

I need to run a query that sets Track Inventory By Size and Color=1 for all products from a particular manufacturer

Can somebody provide me with the sql query to run where I only need to change the manufacturer id? I wish I knew how to write SQL!

I am using 9.2

Thanks!

Try

Code:

UPDATE Product SET TrackInventoryBySizeAndColor = 1
FROM Product p
INNER JOIN ProductManufacturer pm on pm.ProductID = p.ProductID
where pm.ManufacturerID = 0

Replace 0 with an actual ManufacturerID.

I really appreciate your help. I ran the query and it worked. However, what we are trying to do is to manage inventory at the size level and it only shows on the admin side if there is a value for the SizeSKUModifiers field. At the time of importing the products we only imported the sizes but not the SizeSKUModifiers. How can we populate the SizeSKUModifiers with the same values as in the Size field?

Thanks!

Honestly since imports can update existing products. I would lean towards doing another import that updates the products. But if you must do it through SQL. Try Something like this:

Code:

UPDATE pv SET SizeSKUModifiers=Sizes
FROM ProductVariant pv
where Deleted=0 and DATALENGTH(SizeSKUModifiers)=0 and DATALENGTH(Sizes)<>0

Man, this is like pulling teeth. The SQL worked. Thanks for that.

However, unless I open the product page in the admin and click "Update Product" manually, the inventory fields for each size will not show under Manage Manufacturers -> Products -> Inventory

The problem is that I will have to do this manually for hundreds of products.

I have reset the cache, restarted the server, ran the monthly maintenance but nothing updates the products like a manual update.

Any ideas?

Thanks again!