Multiple product attribute updates

I've been having a look in the DB, attempting to update the same product attribute for multiple products simultaneously.

Is this possible?

I was hoping something like this would work (it doesn't):

update [dbo].[Product_ProductAttribute_Mapping] set Id = 186 where ProductId IN (70, 71, 72) and ProductAttributeId = 1;

and then just editing the one set of product attribute options in [dbo].[ProductAttributeValue]

john.dent wrote:
In the nop database the Id field is always the Primary Key for the table so you can't have multiple rows with the same Id by definition.

I'm not completely clear from your post if you're just trying to do a one off update of a few product attribute values or if you're trying to make a permanent change to the relationship so that a single value is mapped to multiple products and future updates to the value would affect all those products.

If it's the latter then it's not possible because of the way the relationships between the Product Attribute tables are defined in the database. For Product Attributes the attribute is linked to the product in the Product_ProductAttribute_Mapping table and the values for the product attribute are then linked to the mapping instance in the ProductAttributeValue table. So the values exist independently of each other.

If you're just trying to make a one off change to a few values that you've already got defined to update the prices/costs/whatever then it's a bit simpler. This SQL should do the job:

BEGIN TRANSACTION

UPDATE pav
   SET pav.AttributeValueTypeId = pav.AttributeValueTypeId
      ,pav.AssociatedProductId = pav.AssociatedProductId
      ,pav.Name = pav.Name
      ,pav.ColorSquaresRgb = pav.ColorSquaresRgb
      ,pav.ImageSquaresPictureId = pav.ImageSquaresPictureId
      ,pav.PriceAdjustment = pav.PriceAdjustment
      ,pav.WeightAdjustment = pav.WeightAdjustment
      ,pav.Cost = pav.Cost
      ,pav.Quantity = pav.Quantity
      ,pav.IsPreSelected = pav.IsPreSelected
      ,pav.DisplayOrder = pav.DisplayOrder
      ,pav.PictureId = pav.PictureId
OUTPUT inserted.*
  FROM ProductAttributeValue AS pav
       JOIN
       Product_ProductAttribute_Mapping AS ppam
       ON ppam.Id = pav.ProductAttributeMappingId
          AND ppam.ProductId IN (1,2,3)   --Ids of Products to be updated
          AND ppam.ProductAttributeId = 1 --Id of Product attribute to be updated
 WHERE pav.Name = 'MyAttributeName'       --Some condition to restrict the values to be updated

ROLLBACK

The bits in italics are where you would enter the new values. The bits in bold are the conditions you would need to edit to make it target just the particular values you're interested in.

I've wrapped the whole thing in a transaction that's set to rollback so it should be fairly harmless in its current form but you'd need to change the rollback to commit to make the change stick. In any case I'd suggest doing this in a development environment until you're confident that it's not going to break something and backing up the database before doing anything in a live environment.

Oh yeah, it's also worth noting that the product import/export functionality in nop 3.8 can now handle product attributes too so it might just be easier (if a bit slower) to do it that way.

petemitch wrote:

Thanks for the reply,

I was hoping to ' make a permanent change to the relationship so that a single value is mapped to multiple products and future updates to the value would affect all those products. '

That's a handy little script for sure, I was hoping to be able to add a new product attribute value to multiple products at the same time...  I think i might have to use multiple insert into's, one for each product.


Here's the use case anyway:

At the minute I have a few tshirt designs in my store and I currently only stock certain colours of tee, I plan on stocking more colours in future and was looking for a script to add the colour option to all the tees at the same time.

Thanks again! :)